Help calculating 12 month trailing sum in table

[murphm6]

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?

[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:

[murphm6]

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?

[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])

[Prestonwu]

what if to sum a measure?

[murphm6]

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]

Hi @murphm6 ,

I think, You might have to use  SUMX or AVERAGEX to get the rolling total

Regards,

[Greg_Deckler]

@Thejeswar Did I miss them adding that function??

[Thejeswar]

@Greg_Deckler ,

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]

@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.

[Thejeswar]

@Greg_Deckler ,

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

[Greg_Deckler]

@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!