Rolling 7-day average
Smooth daily numbers by averaging the last seven days.
Sales 7-Day Rolling Avg =
VAR LastDate = MAX('Date'[Date])
VAR WindowSales =
CALCULATE(
[Total Sales],
DATESINPERIOD('Date'[Date], LastDate, -7, DAY)
)
RETURN
DIVIDE(WindowSales, 7)How it works
The window is the seven days that end on the day shown. Dividing the window's total by 7 gives the average per day, which removes day-of-week spikes from a daily trend line.
What your model needs
- A date table named 'Date' with one row per day and no gaps, marked as the date table in Power BI (Table tools > Mark as date table). - A one-to-many relationship from 'Date'[Date] to the date column of the Sales table (for example Sales[OrderDate]). - Total Sales = SUM(Sales[Amount]) (called [Total Sales]).
Small example
Input data
Day | Total Sales
1 | 10
2 | 20
3 | 30
4 | 40
5 | 50
6 | 60
7 | 70Expected result
Day | 7-Day Rolling Avg
7 | 40Common mistakes and limitations
- The first six days of data have fewer than seven days in the window, so the average is understated. - Days with no sales still count as days in the divisor, which is usually what you want for a calendar-day average.