Forum Discussion
Best way to get rolling 12-month totals
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")
