Cumulative Total of a Measure for last 3 months

[Anonymous]

Hi Team,

I have been working on a report where I'm trying to add a Cumulatve Total of a Measure of last 3 months to a table.  I used the below measure to arrive at cumulative score however its cumulating all the previous rows and not just last 3.

Cumulative Value=

var thismonthyear = SELECTEDVALUE('Date'[Month Year],0)

RETURN

CALCULATE(

SUMX(VALUES('Date'[Month Year]),

'Measures Table'[Measure Value]),

'Date'[Month Year] <= thismonthyear)

I'm looking for a way to sum only previous 3 rows in the cumulative Value

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try my method.

Measure:

Cumulative 3 months = 
Var N1=SUMMARIZE(FILTER(ALL('Date'),[Month Year]<=MAX('Date'[Month Year])),[Month Year],"Sum",[Measure])
Var N2=TOPN(3,N1,[Month Year],DESC)
return
SUMX(N2,[Sum])

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vbaezgarcia]

Hello! I have tried your solution and it worked great! I was wondering how could this be modify to be cumulative 3 months prior to the current month, for example:

Current month = September

Cumulative Last 3 Months = June+July+August

Hope this clarifies my question. thank you in advance.

[Anonymous]

Hello @v-zhangti , I tried your solution and it worked like a charm. Thank you so much .

[PC2790]

Hey @Anonymous ,

Try:

Sales (last 3 months) =
CALCULATE (
    SUM ( Sales[Sales] ),
    DATESINPERIOD ( ‘Date'[Date], MAX ( ‘Date'[Date] ), – 3, MONTH )
)

[Anonymous]

Hi @PC2790 , Thanks for your quick input. Unfortunately field "Measure Value" is a complex measure and not a column hence I'm not able to use it in the above formula you have suggested.  Is there another way ?