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
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.
Total Sales = SUM(Sales[Amount])
Sales 28d = CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -28, DAY)) 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
Write 3M Avg: the average monthly sales over the last 3 months, ending with the current month.
Press Run to see the result.
Last3 is the total of the three months; divide by 3.
Total Sales = SUM(Sales[Amount])
3M Avg =
VAR Last3 = CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH))
RETURN DIVIDE(Last3, 3) 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 , computed for each cell from its last visible date. A moving average divides the window’s total by the number of periods it covers:
which is only fair when all 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
- Write
Sales 7d. - Write
28d vs LY %: the last 28 days against the same 28 days a year earlier. - Why use
MAX('Date'[Date])rather thanTODAY()as the window’s end in a report?
Answers
CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -7, DAY)).VAR Now = [Sales 28d] VAR LY = CALCULATE([Sales 28d], SAMEPERIODLASTYEAR('Date'[Date])) RETURN DIVIDE(Now - LY, LY).- MAX follows the visual (each row, each slicer selection); TODAY would show the same window everywhere.
Check yourself
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.