Calculate 4-, 13-, and 52-Week Moving Averages in an Excel PivotTable
Question details
Calculate rolling weekly averages for 4-week, 13-week, and 52-week periods in an Excel PivotTable.

- Product
- Microsoft Excel PivotTable and Data Model
- Device & OS
- not provided
- Scenario
- The user wants to compare weekly values across multiple rolling periods using date-based PivotTable rows.
- Observed behavior
- The PivotTable should show a moving average for each period alongside the corresponding week.
Check that your Excel version supports the Data Model and Power Pivot measures, and that your source data has a date column and a numeric value column. Prepare a continuous calendar covering complete weeks and enough history for the 52-week calculation.
Create Data Model measures for rolling weekly averages
Use a calendar table and DAX measures to calculate the average of weekly totals for each rolling period.
The example assumes a Sales table with Date and Amount columns and a Calendar table with Date and Week Start columns. Replace these names with your own. It averages weekly totals, treating weeks with no sales as zero.
Use complete Monday-through-Sunday weeks. The calendar should start on a Monday and end on a Sunday, with at least 364 days of history before the first week you want to analyze.
In an Excel table named Calendar, list every date without gaps in the Date column. Add a Week Start column with the formula =[@Date]-WEEKDAY([@Date],2)+1, then fill it down.
Add the Sales and Calendar tables to the Data Model using Power Pivot > Add to Data Model. In Power Pivot > Manage, create a relationship from Calendar[Date] to Sales[Date] using Diagram View. Ensure the source dates do not contain time values.
In Power Pivot, choose Measures > New Measure and enter: 4-Week Average := VAR EndDate = MAX('Calendar'[Date]) RETURN CALCULATE(AVERAGEX(VALUES('Calendar'[Week Start]), COALESCE(CALCULATE(SUM('Sales'[Amount])), 0)), FILTER(ALL('Calendar'), 'Calendar'[Date] > EndDate - 28 && 'Calendar'[Date] <= EndDate))
Use Measures > New Measure twice more. Copy the 4-week formula, rename the first copy 13-Week Average and change 28 to 91; rename the second copy 52-Week Average and change 28 to 364.
Choose Insert > PivotTable and select the workbook's Data Model. Put Calendar[Week Start] in Rows and the three measures in Values. Use the Week Start field rather than automatic date grouping so each row represents a complete week.

Explore weekly spreadsheet analysis with WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and Excel format compatibility for an easier move between applications. It does not fix Excel Data Model or DAX issues.

Frequently Asked Questions
Why do I need a calendar table for the moving averages?
A continuous Calendar[Date] column lets the measure identify every day in the rolling window, including weeks with no sales. Create a relationship from Calendar[Date] to the source table's date column in Power Pivot > Manage.
Are these averages based on days or weeks?
They are averages of weekly totals. The measure sums Sales[Amount] for each Week Start and then averages those totals over 28, 91, or 364 days.
What happens when a week has no sales?
The example uses COALESCE in the DAX measure to count that week's total as zero. Remove COALESCE if you want blank weekly totals excluded from the average.
Why does the 52-week result look wrong for early weeks?
Check that the Calendar table contains at least 364 days ending with each displayed week. If the calendar starts too late, the measure averages only the weeks available.
Can I use Excel's automatic date grouping for these measures?
For the example measures, place the prepared Calendar[Week Start] field in PivotTable Rows instead. This keeps each row aligned to a complete Monday-through-Sunday week.




