DAX moving averages with incomplete trading days

A moving average needs an explicit window and a completeness rule. Three observed rows are not necessarily three expected trading days, and an unknown day's value should not automatically become zero or disappear from the denominator.

Use a trading calendar to identify the expected dates, then require the source coverage your measure promises. A strict complete-window average should remain unavailable when any required day is incomplete.

Import the dedicated coverage fixture

FactTradingCoverage.csv in the time-example lab is a separate six-row exercise table. It defines hypothetical trading dates and a sequential TradingIndex:

February 3 is a confirmed zero. February 4 is incomplete with a blank amount. The skipped weekend is excluded by this hypothetical trading calendar, not because a transaction happened to be absent.

Import Date as Date, TradingIndex and IsComplete as whole numbers, and preserve the blank NetPaise. Use this table directly for the exercise without adding relationships that would introduce extra filters.

Require a full, complete window

The following measure targets a visual with one fixture date or trading index per row:

Strict 3 Trading Day Average =
VAR EndIndex = MAX(FactTradingCoverage[TradingIndex])
VAR WindowRows =
    FILTER(
        ALL(FactTradingCoverage),
        FactTradingCoverage[TradingIndex] <= EndIndex
            && FactTradingCoverage[TradingIndex] > EndIndex - 3
    )
VAR CompleteRows =
    COUNTROWS(
        FILTER(
            WindowRows,
            FactTradingCoverage[IsComplete] = 1
                && NOT ISBLANK(FactTradingCoverage[NetPaise])
        )
    )
RETURN
    IF(
        COUNTROWS(WindowRows) = 3 && CompleteRows = 3,
        AVERAGEX(WindowRows, FactTradingCoverage[NetPaise]),
        BLANK()
    )

At February 6, the window includes February 4, 5 and 6. It is incomplete and should remain blank. At February 9, the window contains 20,000, 15,000 and 5,000, averaging approximately 13,333.33 paise.

The first two dates also remain unavailable because fewer than three expected rows exist. Microsoft documents the iterator used for the final calculation in its AVERAGEX reference.

Understand the scope of ALL

ALL(FactTradingCoverage) lets the measure retrieve preceding fixture rows even when the current visual row filters one date. In this isolated table that is the intended exercise scope.

Do not copy the expression unchanged into a multi-store fact table: clearing the entire table could remove store or other eligibility filters. A production design should construct its date window while preserving the relevant business dimensions and completeness grain.

Also decide what the measure's total row means. As written, it uses the maximum visible TradingIndex, so a total represents the endpoint's rolling value, not a sum of daily averages. Label or suppress that total if it would confuse readers.

Preserve zero and unknown separately

If February 4 were incorrectly filled with zero, the February 6 average would become 11,666.67. If a naive average ignored its blank and used only the two observed days, the result would become 17,500. Neither represents a complete three-day average under the stated contract.

The correct unavailable state tells the reader that the window cannot yet support the promised measure. A separate status can identify the missing date and expected source.

Test corrections and calendar changes

When February 4 becomes complete, replace its blank with the verified value and recalculate affected windows. Preserve evidence that earlier displayed results were provisional or unavailable.

If the trading calendar changes, recompute indices and review historical windows. An index is a representation of the calendar contract, not an immutable fact about dates.

Python verifies the fixture and February 9 arithmetic in time-example-results.json. DAX and visual context still require Power BI application review.

Exercise: supply a verified February 4 value of 9,000. The February 6 three-day average should become 44,000/3, approximately 14,666.67. Confirm that February 9 remains unchanged because February 4 is outside its window.

NeuraPath's Data Analytics with Generative AI course connects DAX windows with source completeness. A reliable moving average makes missing evidence visible instead of smoothing it away.

Continue learning

This article is part of the DAX measures and analytical correctness sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Compare same-period sales when calendars differ.
• Continue with ALL versus ALLSELECTED in a share-of-total measure.