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.

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

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.