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

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    v-juanli-msft 

     

    Hi Meggie, I was wondering if it is possible so the values for 2019 only and mabke it blank to others years in the past?

    I need to get one line chart start at 2019 like in Screenshot below.

     

    TIA,

    Sam

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      6 years ago

      Hi Anonymous 

      How about this?

      Measure =
IF(YEAR(MAX('Fact'[related date]))=YEAR(Today()),
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.