Forum Discussion
Helper IIHelp calculating 12 month trailing sum in table
Hi all, i want to calculate a twelve month trailing sum of the volume column for each individual prod pkg code - how can i do this?
I've tried doing CALCULATE(SUM('Table' [Vol(CUR]), DATESINPERIOD('Table' [Date], Max([Date]), -12, MONTH)
but that isn't working.. any suggestions?
11 Replies
- Greg_Deckler
Community Champion
Thejeswar Did I miss them adding that function??
- Thejeswar
Super User
Not sure on your ask. As murphm6 was asking for getting the prior 12 months number, I thought RunningTotal() should be an appropriate thing for it. But I see you have suggested a different approach for this. If that works for Rolling Totals, I am good to know it for my future use.
In your response, what I was not sure is, Why do you have to use AVERAGEX over SUMX to get the Rolling Sum.
And, BTW, in my response, I had earlier wrongly mentioned as Rolling Average in place of Rolling Sum
- Greg_Deckler
murphm6 Try this, just use SUMX instead of AVERAGEX: Better Rolling Average - Microsoft Power BI Community
Also, it's not working because it's a single table data model which CALCULATE doesn't handle very well:
murphm6I know the result for this is a measure - is there any way to get this value as a column? So for each row, the column would be a sum of the volume for the prior 12 months?
- Greg_Deckler
murphm6 It's almost the exact same formula, you just have to drop the MAX aggregation:
Better Rolling Sum = VAR __EndDate = 'Table'[Date] VAR __3MonthsAgo = EOMONTH(__EndDate, -12) VAR __StartDate = DATE(YEAR(__3MonthsAgo), MONTH(__3MonthsAgo), 1) VAR __Table = SUMMARIZE( FILTER(ALL('Table'),[Date]>=__StartDate && [Date]<=__EndDate), 'Table'[Month], "__Value",SUM('Table'[Value]) ) RETURN SUMX(__Table,[__Value])
