Calculate a Three-Month Moving Average in an Excel PivotTable
Question details
Calculate a reliable rolling three-month average from PivotTable data, including periods where no source records exist.

- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- The user needs a monthly three-month moving average that remains aligned to calendar months.
- Observed behavior
- A standard PivotTable calculated field does not reliably calculate a time-based rolling average because it evaluates individual source rows instead of the displayed sequence of months.
Ensure the source dates are valid Excel dates and are grouped or represented at the month level. Build a calendar list that includes every month in the reporting period, including months with no transactions.
Use a Calendar Table and Power Pivot Measure for the Rolling Average
Use this method when your workbook uses the Excel Data Model and you need a dynamic moving average that responds to PivotTable filters and slicers.
A PivotTable calculated field cannot evaluate a rolling window of displayed months. A Power Pivot measure can calculate the prior three calendar months when a complete calendar table is related to the fact table.
On a new worksheet, create a Calendar table with one row for every date or month in the reporting range. Select the list and choose Insert > Table, then give the table a name such as Calendar.
Select the source data table, then choose Power Pivot > Add to Data Model. Add the Calendar table in the same way, and make sure both tables contain matching date fields.
Open Power Pivot > Manage > Diagram View, then drag Calendar[Date] to the matching date column in the source data table. Use the Calendar month field in the PivotTable rows rather than the source table month field.
In the Power Pivot calculation area, create a measure such as 3-Month Moving Average := AVERAGEX(DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -3, MONTH), CALCULATE(SUM(Sales[Amount]))). Replace Sales[Amount] with the field you need to average.
Insert or refresh the PivotTable from the Data Model, place Calendar month in Rows, and drag the 3-Month Moving Average measure to Values.

Build a Formula-Based Moving Average Next to the PivotTable
Use this method when Power Pivot is unavailable or when you want a transparent worksheet calculation based on a complete month list.
Create a Three-Month Moving Average from PivotTable Results in WPS Spreadsheets
WPS Spreadsheets can create a complete month-by-month calculation table beside a PivotTable and calculate a stable rolling three-month average with worksheet formulas. This is especially useful when missing months must remain part of the reporting timeline.
- 1. Open the Excel workbook in WPS Spreadsheets: Launch WPS Office, choose Open, and select the .xlsx or .xls workbook containing the PivotTable.
- 2. Create a complete month list: Add a worksheet and enter each reporting month in chronological order, including months with no data records.
- 3. Link monthly totals to the PivotTable: In the adjacent column, enter a GETPIVOTDATA formula that references a cell inside the PivotTable and returns the value for each listed month. Wrap it with IFERROR if blank months should count as zero.
- 4. Calculate the three-month average: In the third month row, use an AVERAGE formula over the current month and the prior two monthly values, then drag the fill handle down the column.
- 5. Refresh and review the results: Right-click the PivotTable and choose Refresh, then confirm that the monthly values and rolling-average formulas update correctly.

Frequently Asked Questions
Why does an Excel PivotTable calculated field not work for a three-month moving average?
A calculated field evaluates source-data rows, not a rolling range of displayed PivotTable months. Use a Data Model measure or calculate the rolling average in a separate formula table.
How do I prevent missing months from shifting my moving average?
Create a calendar or month list containing every period, then calculate from that list. For a formula approach, use GETPIVOTDATA with IFERROR(...,0) when missing months should be counted as zero.
Should months with no sales be counted as zero in the moving average?
It depends on your reporting rule. Count them as zero when no sales means zero activity; exclude them only when the month is not applicable or should not affect the average.
Can a Power Pivot moving-average measure respond to slicers?
Yes. When the PivotTable uses a related calendar table and the measure is created in the Data Model, the measure can recalculate according to applicable PivotTable filters and slicers.
What field should I put in PivotTable rows for a calendar-based moving average?
Use the month field from the complete Calendar table, not the month field from the transaction table. This preserves months that have no matching source records.




