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.