cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Cumulative Total of a Measure for last 3 months

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

1 ACCEPTED SOLUTION
Community Support

Hi, @jatin_

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.

5 REPLIES 5
Community Support

Hi, @jatin_

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.

Regular Visitor

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.

Frequent Visitor

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

Community Champion

Hey @jatin_ ,

Try:

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

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 ?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.