Pivot Table Issues

How to Create an August-to-July Running Total in an Excel PivotTable

WPS Editor Sep 28, 2026 871 views

Question details

The user needs to calculate a continuous running total in a PivotTable based on a fiscal year that runs from August to July.

How to Create an August-to-July Running Total in an Excel PivotTable

Product: Excel
Device & OS: not provided
Scenario: Creating financial reports or tracking continuous data based on an August-to-July fiscal calendar instead of the standard calendar year.
Observed behavior: Standard Excel PivotTable date grouping automatically resets the running total in January, breaking the continuous calculation required for an August-to-July financial calendar.

Before you start

Ensure that the Power Pivot add-in is enabled in Excel, as calculating a continuous custom fiscal running total requires the use of the Excel Data Model and DAX formulas.

Solution 1Recommended

Use a Financial Calendar and Data Model DAX Measure

Create a dedicated financial calendar table and link it to your source data via the Data Model to calculate a continuous August-to-July running total.

By default, Excel groups dates by the standard calendar year. To prevent running totals from resetting in January, you must bypass automatic grouping by defining a custom fiscal calendar and using Power Pivot to manage the relationships and calculations.

Create a financial calendar table

In a new worksheet, create a table containing continuous dates and a corresponding column that defines your custom August-to-July fiscal year and fiscal month.

Add tables to the Data Model

Select your original source data and your new financial calendar table one by one. Go to the Power Pivot tab on the ribbon and click 'Add to Data Model' for both.

Create a table relationship

Open the Power Pivot window and switch to Diagram View. Click and drag the Date field from your financial calendar table to the corresponding Date field in your source data to establish a relationship.

Insert a Data Model PivotTable

Return to the main Excel window, go to the Insert tab, click 'PivotTable', and choose 'From Data Model'. Place it on your desired worksheet.

Apply a DAX measure for the running total

Use the fiscal year and month fields from your financial calendar table in the PivotTable's Rows area. Then, create a new DAX measure in Power Pivot using CALCULATE and custom FILTER functions to compute the running total strictly across the August-to-July timeframe.

Use a Financial Calendar and Data Model DAX Measure

Alternative Method: If you are not familiar with DAX, you can achieve a similar result by adding a 'Fiscal Year' and 'Fiscal Month' helper column directly into your source data using IF formulas, and then using the standard 'Show Values As > Running Total In' feature.

Analyze Data with WPS Spreadsheet

Calculate Custom Running Totals Easily in WPS Office

You can calculate an August-to-July running total in WPS Spreadsheet without needing complex DAX measures. By adding a simple helper column to your source data, you can perfectly group and calculate fiscal totals using standard PivotTable features.

1. Add a helper column: Open your dataset in WPS Spreadsheet and insert a new column named 'Fiscal Year' next to your date column.
2. Apply a fiscal year formula: Assuming your date is in cell A2, enter the formula =IF(MONTH(A2)>=8, YEAR(A2)&"-"&YEAR(A2)+1, YEAR(A2)-1&"-"&YEAR(A2)) to assign each date to the correct August-July period.
3. Insert the PivotTable: Select your entire data range, navigate to the Insert tab, and click 'PivotTable' to create a new report.
4. Configure rows and values: In the PivotTable Fields pane, drag your new 'Fiscal Year' and 'Date' fields to the Rows area, and your numeric field to the Values area.
5. Set the running total: Right-click on a number in the Values column, select 'Value Field Settings', go to the 'Show values as' tab, and choose 'Running Total in' based on the Date field.

Free and lightweight spreadsheet softwareFully compatible with Microsoft Excel .xlsx filesCreate and manage PivotTables with a highly familiar interfaceEasily group and filter data without complex coding or Data Models

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable running total reset in January?

By default, Excel PivotTables automatically group date fields by the standard calendar year (January to December). When a new calendar year begins, the running total calculation automatically resets to align with the new grouping.

Do I need Power Pivot to calculate a fiscal year running total?

Not necessarily. While Power Pivot and DAX offer a robust way to handle complex fiscal calendars dynamically, you can also use standard PivotTables by adding a helper column to your raw data that manually calculates and defines the custom fiscal year.

What is the Excel Data Model?

The Data Model is an advanced Excel feature that allows you to integrate data from multiple tables, building relational data sources inside a single workbook. This enables you to perform complex PivotTable analysis across millions of rows without using traditional VLOOKUP formulas.

Can I change the default fiscal year starting month in a standard PivotTable?

No, standard Excel PivotTables do not have a built-in configuration to change the default start month for native date grouping. You must bypass automatic grouping by either utilizing a helper column in your source data or leveraging the Data Model.

Excel & Data Problems