Forum Discussion
Best way to get rolling 12-month totals
I am using Excel for Mac version 16.102.
I have data in a Table which is basically date, category & value. There are many other columns I use for filtering.
I pivot this data to get totals by month within year but I hide the yearly totals and just have the grand total. My pivot table is filtered to include\remove high-level category data.
I have a Timeline which allows me to select the date range of my pivot.
I have a number of slicers to facilitate filtering to get the dataset totals I need.
There maybe a better way but the above works.
My ask is how do I go from the above to easily get a rolling 12-month totals for say Jan 24 - Dec 24, Feb 24 - Jan 25, Mar 24 - Feb 25 etc etc?
I can move the timeline which gives me the correct answer but I'd like to be able to see the rolling totals for the last 12-months at a glance without having to keep moving the timeline. Eventually I'd like to graph the last 12-months so I can see movement over time.
Can I do want I want with a pivotable or do I need another approach?
Thank you for your help.
8 Replies
At the simplest level, this has worked for me for years.
=SUM(C207:C218)
I'm at row 218 right now in a spreadsheet containing a multi-year monthly summary of increases/decreases in my IRA. There's a column for changes in market value, a column for dividends and interest, for withdrawals. Then, off to the side, I calculate the rolling 12 month totals in each of those categories.
So, to spell this out one step further, C207 contains the monthly market increase for October, 2024; C218 contains September 2025 data. The intervening rows contain the other ten months.
Since each row is a single monthly total, copying the formula down changes the relative references so they always are the rolling 12 months including the last or current.
As noted, this is the ultimate in simplicity and assumes that each row we're working with already contains one month, and that monthly rows are contiguous with no blanks or other extraneous rows.
That is precisely the way I checked my formula!
So, Peter, wouldn't the principle of "Occam's Razor" apply -- use the simplest solution. (?)
😉
To create and aggregate a rolling range, I use a TAKE/DROP combination.
=LET( counter, SEQUENCE(ROWS(amounts)), MAP( counter, LAMBDA(k, IF( k >= 12, SUM(TAKE(DROP(amounts, k - 12), 12)), "" ) ) ) )It may help if you're able to share an anonymized workbook that shows your data arrangement. A rolling average (or any other aggregation) with a 12-month window is very do-able. I have a generalized Lambda that can pull those results.
Rather than posting data, let me try this another way
These two SUMIFS formulas work. They represent the filters I apply to a pivotable. If I have the pivot timeline to show 12 months the inflow and outgoings totals are the same as these SUMIFS formulas.
The problem with my pivot is that it only has one Grand Total, so to see the 12 month totals ending Aug-25 I have to change the timeline.
I can create a table of SUMIFS formulas and create a graph, this all works perfectly but to change the filter I have to edit the formulas which is not very flexible.
So my question is there a way to get a pivot to show rolling 12 month data?
Thank you for your help.
This calculates the Inflow from my data
A18=01/09/2025, G1=12
=SUMIFS(DataTable[NewValue],
DataTable[surplusTransactions],"Include",
DataTable[EndOMonth],"<="&EOMONTH($A18,0),
DataTable[EndOMonth],">="&EOMONTH($A18,-($G$1-1)),
DataTable[accountType], "Budget",
DataTable[New-CategoryGroup], "Inflow",
DataTable[accountType], "Budget",
DataTable[taggedData], "<>"&"Money from Bonus")This calculates the Outgoings
=SUMIFS(DataTable[NewValue],
DataTable[surplusTransactions],"Include",
DataTable[EndOMonth],"<="&EOMONTH($A18,0),
DataTable[EndOMonth],">="&EOMONTH($A18,-($G$1-1)),
DataTable[accountType], "Budget",
DataTable[New-CategoryGroup], "<>"&"Inflow",
DataTable[New-CategoryGroup], "<>"&"Holidays",
DataTable[New-Category], "<>"&"Savings",
DataTable[New-Category], "<>"&"Aviva Pension",
DataTable[New-Category], "<>"&"New Car")
How about adding a Helper Column for Rolling Totals:
To automate rolling 12-month totals and make them graphable:
- Create a Helper Column in Your Source Table
Add a column called Rolling 12M Total. Use a formula like this (assuming your table is named Data and your date column is Date):
=SUMIFS(Data[Value], Data[Date], ">=" & EDATE([@Date], -11), Data[Date], "<=" & [@Date])This formula sums the values from the current row’s date back 11 months, giving you a 12-month rolling total.
- Add a Month Column
Create a column like MonthLabel with:
=TEXT([@Date], "mmm yy")This helps you group and visualize data by month.
- Use a Pivot Table or Chart
Now you can:
- Create a pivot table using MonthLabel as rows and Rolling 12M Total as values.
- Or build a line chart directly from the table to show movement over time.
