Getting 12 Months Running Total

[Sam02]

Hello all,

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

[Greg_Deckler]

There is a Running Total quick measure. Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Sam02]

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

[Sam02]

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 · Accepted solution]

Hi @Sam02 

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.

[Sam02]

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

Hi @Sam02 

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.

[Sam02]

Thanks a lot Maggie! That worked fine.

[Sam02]

Link to sample pbix can be found here:

https://saxonypartners-my.sharepoint.com/:u:/p/subash_gautam/ESUMWEDjIdRJmrQ5nmQoj7sBC5goH31EfBsoyzb...