All lessons

Module 6: Time intelligence · Lesson 6.3 · about 15 min

Rolling 28 days and moving averages

Rolling windows smooth out weekday and holiday noise. Use DATESINPERIOD for the last 28 days, the last 3 months and moving averages, and learn why the first periods need care.

Module 6 · about 15 minutes · read first: 6.1. You’ll be able to write rolling totals and moving averages that are fair to compare.

1. Why this matters

Daily sales jump around with weekdays, weather and holidays. Operations managers watch rolling 7- or 28-day totals because they show the trend without the noise.

2. Last 28 days

Try it: write the DAX

Write Sales 28d: sales in the 28 days up to the last visible date, with DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -28, DAY).

Press Run to see the result.

On each month’s row, MAX('Date'[Date]) is that month’s last day, and the window is the 28 days ending there. Trace a row to see the dates CALCULATE keeps. Every 28-day window has exactly four of each weekday, so a window ending on a Tuesday compares fairly with one ending on a Sunday.

3. Moving average

Try it: write the DAX

Write 3M Avg: the average monthly sales over the last 3 months, ending with the current month.

Press Run to see the result.

Look at January and February 2024: the window reaches back into months with no data, but the measure still divides by 3, so the first values are far too low. Fix it by dividing by the number of months that have sales:

3M Avg =
VAR Window = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH)
RETURN AVERAGEX(
    CALCULATETABLE(VALUES('Date'[YearMonth]), Window),
    [Total Sales]
)

AVERAGEX averages the monthly totals and skips months that are blank. (Run it in the editor above: replace the measure and compare January 2024.)

Principle

Principle: a rolling window is a filter of the dates in (dmax⁡−n,dmax⁡], computed for each cell from its last visible date. A moving average divides the window’s total by the number of periods it covers:

MAk(t)=1k∑i=0k−1St−i

which is only fair when all k periods exist.

4. Worked example

Snow shovels. Daily sales of shovels are zero most of the summer and spike after snowfalls. A rolling 28-day total of shovel units smooths the spikes into a clear winter hump, which tells the buyer when to order stock. Try it in the playground: Shovels 28d = CALCULATE(SUM(Sales[Quantity]), Product[Product] = "Snow shovel", DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -28, DAY)) split by YearMonth.

5. Practice

  1. Write Sales 7d.
  2. Write 28d vs LY %: the last 28 days against the same 28 days a year earlier.
  3. Why use MAX('Date'[Date]) rather than TODAY() as the window’s end in a report?
Answers
  1. CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -7, DAY)).
  2. VAR Now = [Sales 28d] VAR LY = CALCULATE([Sales 28d], SAMEPERIODLASTYEAR('Date'[Date])) RETURN DIVIDE(Now - LY, LY).
  3. MAX follows the visual (each row, each slicer selection); TODAY would show the same window everywhere.

Check yourself

1What does DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -28, DAY) return?
2Why do retailers like 28-day windows?
3A 3-month average for the very first month of data is far too low. Why?

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.