Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Getting 12 Months Running Total

Hello all,

need to create a measure that would return 12 month running total for each month like in screenshot below.

 
Need Help
Show and Tell
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create a measure

    Measure =
CALCULATE (
    SUM ( 'Fact'[Value] ),
    FILTER (
        ALLSELECTED ( 'Fact' ),
        'Fact'[related date] <= MAX ( 'Fact'[related date] )
            && DATEDIFF ( 'Fact'[related date], MAX ( 'Fact'[related date] ), MONTH ) <= 12
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your reply Greg, the quick measure did not worked and I created another one my self using different option both gave me same incorrect result.

       
      Quick Measure =
      CALCULATE(
          [Sum of value],
          FILTER(
              ALLSELECTED('Period'[Period]),
              ISONORAFTER('Period'[Period], MAX('Period'[Period]), DESC)
          )
      )
       
      Using my Dax
      Running Total =
      CALCULATE([Sum of value],DATESBETWEEN(Period[FirstDayOfMonth],MINX(ALL(Period),Period[FirstDayOfMonth]),MAX(Period[FirstDayO
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        following measure gives me Running total for continious period of time, Can some one help me to modify it for getting running total upto last 12 months only?

         

        Running Total =
        CALCULATE([Sum of value],
        FILTER(ALL(Period),Period[FirstDayOfMonth] <= MAX (Period[FirstDayOfMonth])),
        VALUES(Period[FirstDayOfMonth].[Year]),
        VALUES(Period[FirstDayOfMonth].[Month])
        )