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,
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 ) )
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..