Forum Discussion

murphm6's avatar
murphm6
Icon for Helper II rankHelper II
3 years ago

Help 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]), DATESINPERIO...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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

 

 

murphm6's avatar
murphm6
Icon for Helper II rankHelper II
3 years ago

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?