DAX patterns: running totals, YoY and moving averages

Certain calculations appear in almost every Power BI project. This tutorial presents three of the most common — running totals, Year-over-Year comparisons and moving averages — with the correct formulas and practical variations.

1. Running Total

Running Sales =
CALCULATE(
    SUM(fSales[Revenue]),
    FILTER(
        ALL(dDate[Date]),
        dDate[Date] <= MAX(dDate[Date])
    )
)

It works correctly with any date filter in the report because it uses ALL(dDate[Date]) and then applies the cumulative date condition.

2. Year-over-Year (YoY)

Sales YoY Abs =
VAR vCurrent = SUM(fSales[Revenue])
VAR vPrior = CALCULATE(
    SUM(fSales[Revenue]),
    SAMEPERIODLASTYEAR(dDate[Date])
)
RETURN vCurrent - vPrior

Sales YoY % = DIVIDE([Sales YoY Abs], [Sales Prior Year], BLANK())

3. Moving Average (N periods)

Moving Avg 3M =
VAR vMaxDate = MAX(dDate[Date])
VAR vMinDate = EDATE(vMaxDate, -2)   -- 3 months back
RETURN
CALCULATE(
    AVERAGEX(
        VALUES(dDate[Year] & "-" & dDate[Month]),
        [Total Sales]
    ),
    DATESBETWEEN(dDate[Date], vMinDate, vMaxDate)
)

Useful variations

• Running total by category: add an ALLEXCEPT to preserve the category filter.
• YoY with fiscal period: use DATEADD(dDate[Date], -1, YEAR) instead of SAMEPERIODLASTYEAR when the fiscal year does not match the calendar year.
• 7-day moving average: replace EDATE(vMaxDate, -2) with vMaxDate - 6.

Conclusion

These three patterns cover most of the time-based analyses requested by managers and analysts. Keep them in a personal reference file — you will reuse them in practically every project.