Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @Anonymous
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 ) )
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..
Hi I have the same problem. Can someone help?
Thank you very much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |