How to calculate weighted moving average in DAX: step by step

Learn how to calculate a weighted moving average in DAX to smooth time series and give more weight to recent observations. This technique is useful for sales, user metrics or any series where you want recent events to have more influence on the result.

Prerequisites

• Power BI Desktop (or another tool that supports DAX).
• A fact table with columns: Date (Data type) and Value (numeric).
• Date table related to the fact table.

Step 1: Understand the weighted moving average logic

Unlike the simple moving average, the weighted moving average applies different weights to each point within the window (for example, last 3 or 7 days). The weights should sum to 1 or be normalized in the formula. Decide the window and the sequence of weights (e.g.: 0.5, 0.3, 0.2 for 3 periods: more weight on the most recent).

Step 2: Create the sum measure for the period

First create a base measure that sums the values. This provides a clean reference to use inside the windows.

Total Value = SUM('Facts'[Value])

Step 3: Fixed weighted moving average measure (e.g.: 3 periods)

For a fixed 3-period window with weights [0.5, 0.3, 0.2] — where the most recent receives 0.5 — use time intelligence/context functions. This measure assumes the date table is related and the current selection of Date is the end point of the window.

MMW 3 períodos =
VAR w1 = 0.5  -- peso para data actual
VAR w2 = 0.3  -- peso para 1 período atrás
VAR w3 = 0.2  -- peso para 2 períodos atrás
VAR d0 = MAX('Date'[Date])
VAR v0 = CALCULATE([Total Value], 'Date'[Date] = d0)
VAR d1 = CALCULATE( MAX('Date'[Date]), FILTER(ALL('Date'), 'Date'[Date] < d0))
VAR v1 = CALCULATE([Total Value], 'Date'[Date] = d1)
VAR d2 = CALCULATE( MAX('Date'[Date]), FILTER(ALL('Date'), 'Date'[Date] < d1))
VAR v2 = CALCULATE([Total Value], 'Date'[Date] = d2)
VAR numerator = w1 * v0 + w2 * v1 + w3 * v2
VAR denom =
    (IF(NOT(ISBLANK(v0)), w1, 0)) +
    (IF(NOT(ISBLANK(v1)), w2, 0)) +
    (IF(NOT(ISBLANK(v2)), w3, 0))
RETURN
    IF(denom = 0, BLANK(), numerator / denom)

Step 4: Generic measure for N periods with decreasing weights

For a more flexible solution, build a measure that dynamically creates the N-period window and applies linearly decreasing weights (e.g.: N, N-1, ..., 1). This technique is useful when you don't want to manually specify each weight.

MMW N períodos (linear) =
VAR N = 7  -- ajusta para 3, 7, 14, etc.
VAR EndDate = MAX('Date'[Date])
VAR WindowDates =
    TOPN(
        N,
        FILTER(ALL('Date'), 'Date'[Date] <= EndDate),
        'Date'[Date], DESC
    )
VAR TableWithValues =
    ADDCOLUMNS(
        WindowDates,
        "ValueOnDate", CALCULATE([Total Value], 'Date'[Date] = EARLIER('Date'[Date]))
    )
VAR Ranked =
    ADDCOLUMNS(
        TableWithValues,
        "Rank", RANKX(WindowDates, 'Date'[Date], , DESC, DENSE)
    )
VAR WithWeights =
    ADDCOLUMNS(
        Ranked,
        "Weight", DIVIDE( (N + 1 - [Rank]), SUMX(Ranked, N + 1 - [Rank]) )
    )
VAR Numerator = SUMX(WithWeights, [Weight] * [ValueOnDate])
VAR Denominator = SUMX(WithWeights, [Weight])
RETURN
    IF(Denominator = 0, BLANK(), Numerator / Denominator)

Step 5: Common errors and how to avoid them

Frequent errors include: not having a complete date table (missing days), using MAX without correct context, or division by zero when data is missing. The measures above use ALL('Date') and denom = 0 checks to avoid these issues. Confirm that the relationship between the fact table and the date table is active and one-to-many.

Verify the result

Create a line chart with 'Date'[Date] on the axis and the MMW measure in values. Compare with the original series (Total Value) to see the smoothing. Use date range slicers and check extremes (start of the series) to confirm the measure handles incomplete windows correctly (it should show normalized values or BLANK, depending on the choice).

Conclusion

You now have two approaches for weighted moving averages in DAX: a fixed one and a generic one with linear weights. Next steps: experiment with different weighting schemes (exponential), use the SAMEPERIODLASTYEAR function for comparisons and optimize performance with indexes on large tables. Tip: start with a small N and validate each step with a table visual showing dates, values and weights for debugging.