Pivot Table Issues

How to Create a Rolling Weekly Average in an Excel PivotTable

Nimra Malik Oct 1, 2026 868 views

Question details

The user needs to calculate rolling weekly averages and sales variance by client and product type inside an Excel PivotTable.

How to Create a Rolling Weekly Average in an Excel PivotTable

Product: Excel
Device & OS: not provided
Scenario: Analyzing weekly sales data to track ongoing performance trends and calculate the variance between current sales and historical weekly averages.
Observed behavior: The goal is to dynamically compare each week's sales against a rolling historical average instead of a static overall average.

Before you start

Ensure your Excel version supports Power Pivot and that your source data is formatted as an Excel Table. You will also need a dedicated Date (Calendar) table in your Data Model to use time intelligence functions properly.

Solution 1Recommended

Use Power Pivot and DAX Measures to Calculate Rolling Averages

The most robust method for creating rolling averages in a PivotTable is to load your data into the Data Model and use Data Analysis Expressions (DAX) to handle the time intelligence calculations.

By utilizing Power Pivot, you can create a measure that dynamically calculates the average over a specified number of previous weeks.

This approach prevents the need for manual helper columns and automatically updates when filtered by client or product type.

Load Data to the Data Model

Select your sales data table, go to the 'Power Pivot' tab on the ribbon, and click 'Add to Data Model'.

Create a Calendar Table

In the Power Pivot window, go to 'Design' > 'Date Table' > 'New' to generate a continuous calendar table. Ensure a relationship is created between your Sales Date column and the Calendar Date column.

Define the Weekly Sales Measure

In the calculation area, create a basic DAX measure for total sales, for example: Weekly Sales:=SUM(Sales[Amount]).

Write the Rolling Average DAX Measure

Create a new measure using CALCULATE and DATESINPERIOD. For a 4-week rolling average, use a formula similar to: RollingAvg:=CALCULATE([Weekly Sales], DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -28, DAY)) / 4.

Calculate Variance

Create another measure to find the variance by subtracting the rolling average from the current weekly sales: Variance:=[Weekly Sales] - [RollingAvg].

Build the PivotTable

Insert a PivotTable from the Data Model. Place your Calendar Date hierarchy in the Rows area, and add your new Weekly Sales, Rolling Average, and Variance measures to the Values area.

Use Power Pivot and DAX Measures to Calculate Rolling Averages

Adjusting the Rolling Period: The DAX formula can be customized for different rolling periods (e.g., 8 weeks, 12 weeks) by adjusting the number of days in the DATESINPERIOD function and modifying the divisor accordingly.

Calculate Rolling Averages Using Helper Columns in WPS Office

If you do not want to use complex DAX formulas, you can easily calculate a rolling weekly average directly in your source data using standard formulas in WPS Spreadsheet, then summarize it using the built-in PivotTable feature.

1. Add a Helper Column: In your WPS Spreadsheet source data, insert a new column next to your weekly sales data and name it 'Rolling Average'.
2. Apply the AVERAGEIFS Formula: Use the AVERAGEIFS function to calculate the average sales for the past X weeks based on the date, client, and product type criteria directly in the cell.
3. Insert the PivotTable: Select your updated dataset, navigate to the 'Insert' tab, and click on 'PivotTable'.
4. Configure the PivotTable Fields: Drag your Date field to the Rows area, and place your 'Weekly Sales' and 'Rolling Average' helper columns into the Values area. Ensure they are set to 'Sum' or 'Average' as needed.

100% compatible with Microsoft Excel (.xlsx, .xls) formatsEasily perform advanced calculations using built-in formulas like AVERAGEIFSLightweight application that runs smoothly on all devices without requiring add-ins

microsoft office alternative - wps office

Frequently Asked Questions

Can I calculate a rolling average in an Excel PivotTable without using Power Pivot?

Yes, but you will need to add helper columns to your source data. By using formulas like AVERAGEIFS or OFFSET, you can calculate the rolling average row-by-row before creating the standard PivotTable.

Why is my DAX measure returning an error for the rolling average?

This usually happens if your Calendar table is not properly related to your fact (sales) table, or if the date column isn't officially marked as a 'Date Table' in the Data Model settings.

How do I handle missing weeks or zero sales in my rolling average calculation?

Using a continuous Calendar table in the Data Model ensures that time intelligence functions in DAX evaluate the date range correctly. Even if specific weeks have zero sales, the time window will calculate accurately without skipping weeks.

Excel & Data Problems