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
  • murphm6's avatar
    murphm6
    Icon for Helper II rankHelper II
    3 years ago

    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'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?