cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Liism Regular Visitor
Regular Visitor

Cumulative total of 3 years

Hi, 

 

I would need to make a measure that always sums up the last 3 years. For example, for March 2018 the cumulative total is from March 2015 - March 2018, for April 2018 it should be from April 2015 - April 2018 etc. 

 

Usually I have used YTD functions for cumulative totals, but in this case there are several years involved. 

 

Does anyone have any idea how to get such measure? 

 

Thank you in advance, 

Liis 

2 REPLIES 2
Super User
Super User

Re: Cumulative total of 3 years

Hi @Liism

 

You can use the DATESINPERIOD DAX function...

You shoudl have a calendar table

 

Try this pattern

 

3 years cumulative =
CALCULATE (
    SUM ( Table1[Amount] ),
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, YEAR )
)

 

Liism Regular Visitor
Regular Visitor

Re: Cumulative total of 3 years

Hi, 

 

It's not really working as expected. Firstly, it's calculating the total even if there's one month total only available.. I would like it to only give total if last three years total are available.. Also, I checked the amounts with the Excel I have and they are not matching, so I'm not sure how this measure is summing up the total at the moment..