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.

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

     

     

     

     

     

     
     
    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks a lot Maggie! That worked fine.