Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
@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:
I 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?
@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])
what if to sum a measure?
So i think this is on the right track but it's not accounting for each prod-pkg code, rather it's just summing the entire thing. What can I add to ensure it also considers the prod-pkg-code column?
@Thejeswar Did I miss them adding that function??
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
@Thejeswar I'm just not aware that a RUNNINGTOTAL function actually exists in DAX. If it does, it doesn't show up in my intellisense and there is no documentation on it anywhere that I find.
Sorry for creating a confusion here.😟
Yes you are right... There is no such function. Not sure why gave that reply, must be some confusion that made me put that non-existing function😥
I will correct it
@Thejeswar No worries! I was just like "Holy cow, did I miss them adding a new DAX function?" It's happened before that I find out after the fact! Would be a nice function to have!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |