Running Total Stops at Year End

A quick way to show how amounts accumulate over time is to build a pivot table, and show the values as a running total.

However, if you group the dates by year and month, the running total stops at the end of each year, and starts again at the start of the next year. There is no setting you can adjust to change this behavior.

Create a New Field in the Source Data

If you’d like the running total to continue from one year to the next, you can add a new field in the source data, to calculate the year and month in each record. Then, use that Year-Month field as the base for the running total:

1. Add a column to the source data, with the heading YrMth.
2. Enter a formula to return the year and month of the date in each row. For example, use this formula if the date is in Column A: =TEXT(A2, “yyyy-mm”)
3. Refresh the pivot table, add the YrMthfield to the Row Labels area, and remove the Date and Year fields.
4. Create a running total with YrMth as the base field.

Video: Running Total Stops at Year End

To see the steps for creating a running total that continues from one year to the next, please watch this video tutorial.

Download the Sample File

To download the sample file for this video, please visit my Contextures website: Running Total Year to Year

______________