Pivot Table Issues

Calculate 4-, 13-, and 52-Week Moving Averages in an Excel PivotTable

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 870 views

Question details

Calculate rolling weekly averages for 4-week, 13-week, and 52-week periods in an Excel PivotTable.

Calculate 4-, 13-, and 52-Week Moving Averages 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.
Before you start

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.

Solution 1Recommended

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.

1
Prepare the calendar table

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.

2
Build the Data Model

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.

3
Create the 4-week measure

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))

4
Create the longer measures

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.

5
Build the PivotTable

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.

Create Data Model measures for rolling weekly averages
Check the date range: A week without sales contributes zero to the average. If the calendar does not contain the full preceding period, the measure averages the weeks available instead. Grand totals use the latest calendar date in their filter context and may not represent an average of the displayed rows.
Free Microsoft Office alternative

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.

Review and organize weekly data in WPS SpreadsheetOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar interface for everyday spreadsheet workWork with a free, lightweight Office suite
microsoft office alternative - wps office

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.