Calculation Issues

How to Calculate Two-, Four-, and Six-Year Rolling Averages in Power Pivot

Tauseeq Magsi Oct 21, 2026 868 views

Question details

The user needs to calculate rolling two-, four-, and six-year averages by city and year for a dataset containing over 1,500 rows, without entering values one at a time.

How to Calculate Two-, Four-, and Six-Year Averages in Power Pivot

Product: Excel Power Pivot / Power BI
Device & OS: not provided
Scenario: Analyzing a large multi-field dataset and creating dynamic multi-year summaries in a PivotTable.
Observed behavior: The user is unsure how to properly apply DAX logic to automate these averages, and their system experienced crashing when attempting to process the data in Power Pivot.

Before you start

Ensure that your source data is organized in a clean tabular format without blank rows, and verify that the Power Pivot add-in is enabled in your Excel application.

Solution 1Recommended

Use DAX Measures in the Power Pivot Data Model

By linking your main dataset to a proper Date table, you can write dynamic DAX measures that automatically calculate rolling periods, avoiding the need for manual row-by-row formulas.

For datasets with over a thousand rows and multiple fields, adding manually calculated columns for every multi-year average is highly inefficient. Utilizing DAX measures inside the Data Model ensures your averages calculate dynamically based on the City and Year context applied in your PivotTable.

Create a Dedicated Date Table

Add a continuous Date or Year table to your Excel workbook and add it to the Data Model. This is required for DAX time intelligence functions to operate correctly.

Establish Relationships

Open the Power Pivot window, switch to Diagram View, and drag a relationship line connecting the 'Date' or 'Year' column in your main dataset to the 'Date' column in your new Date table.

Write the Rolling Average DAX Measures

In the calculation area below your data, define your measures using the CALCULATE and DATESINPERIOD functions. For a two-year average, use a formula like: CALCULATE(AVERAGE(Table[Value]), DATESINPERIOD(Date[Date], MAX(Date[Date]), -2, YEAR)). Create separate measures modifying the '-2' to '-4' and '-6'.

Add Measures to a PivotTable

Return to your main Excel sheet and insert a PivotTable directly from the Data Model. Place 'City' and 'Year' in the Rows section, then drop your new DAX measures into the Values section to view the rolling averages.

Use DAX Measures in the Power Pivot Data Model

Addressing System Crashes: If Excel crashes while computing complex DAX measures, you may be hitting the limits of your system's memory. You can use the exact same DAX measures and relationships in Power BI Desktop, which is often more stable for heavy data modeling.

Free Microsoft Office alternative

Need a Reliable and Lightweight Office Suite for Data Analysis?

While advanced add-ins like Power Pivot are powerful, they consume high system resources and often cause crashes. For everyday data summarization and standard PivotTable analysis, WPS Office provides a highly efficient, lightweight, and free alternative that won't slow down your computer.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
2. Open Your Dataset: Launch WPS Spreadsheet and open your existing data files seamlessly without worrying about format issues.
3. Create a Standard PivotTable: Navigate to the 'Insert' tab and select 'PivotTable' to easily group and summarize your multi-year data by city.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Robust built-in PivotTable features for fast and accurate data summarization.Lightweight installation ensures smooth performance without system crashes.Completely free to use with an intuitive, familiar tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why should I use DAX measures instead of adding calculated columns?

DAX measures are calculated on the fly based on the filters applied in your PivotTable, making them highly dynamic and memory-efficient. Calculated columns compute row-by-row and are stored in the file, which increases file size and slows down processing for large datasets.

Why does Power Pivot crash when analyzing large datasets?

Power Pivot consumes a significant amount of RAM. If you are using the 32-bit version of Excel, it limits the application to 2GB of memory, which easily causes crashes during intensive DAX calculations. Using a 64-bit version or switching to Power BI Desktop resolves this.

Is a separate Date table strictly necessary for calculating multi-year averages?

Yes. Standard DAX time intelligence functions rely on a continuous, uninterrupted Date table to accurately calculate moving or rolling periods across years. Without it, functions like DATESINPERIOD will return errors or incorrect aggregations.

Excel & Data Problems