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 there,
Been trying to get the DAX for this but no luck so far. Need some help on the following.
So for each month, I'm trying to Sum the previous 12 months of data. Let's say for September 2019, I need to go back 12 months and sum the number of sales (Oct/2018 to Sept/2019). The same goes for the rest of the months in the past, for Aug/2019 should be Sept/2018 to Aug/2019 and so on for the rest of the months in the past.
Thanks for your help!
Midway
Solved! Go to Solution.
I added some months to make it easier for me to debug the measure
here is the measure:
Help when you know. Ask when you don't!
here's some sample code i found online
Sales12M := CALCULATE ( [Sales], DATESBETWEEN ( Calendar[FullDate], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Calendar[FullDate] ) ) ), LASTDATE ( Calendar[FullDate] ) ) )
Help when you know. Ask when you don't!
Thanks for the reply there, but don't think this is giving me what I need. What would be the Calendar FullDate?
I do have a calendar table tho.
That would be the name of the field in your calendar table that holds the date.
Help when you know. Ask when you don't!
That's what I thought, but still not showing the right numbers. This is suming the last 12 months from current month (October 2019 to October 2018) but this is just repeating the same numbers for the other dates. Like for example Sep-2019 should sum from Sept-2019 to Sept 2018 and so forth.
Can you make a sample pofwer bi file available ?
Help when you know. Ask when you don't!
This is an example of what I'm trying to accomplish. The previous 12 months sum column should sum from current month 12 months in the pat. For the previous month, it should sum from Septeber 2019 to past 12 months and so on for the rest of the months in the past. Let me know if I'm making any sense here please.
Sale Date | Sales | Previous 12 months sum |
Jun-18 | 20 | |
Jul-18 | 18 | |
Aug-18 | 30 | |
Sep-18 | 20 | |
Oct-18 | 22 | |
Nov-18 | 40 | |
Dec-18 | 60 | |
Jan-19 | 21 | |
Feb-19 | 19 | |
Mar-19 | 20 | |
Apr-19 | 56 | |
May-19 | 20 | 346 |
Jun-19 | 78 | 404 |
Jul-19 | 12 | 398 |
Aug-19 | 44 | 412 |
Sep-19 | 11 | 403 |
Oct-19 | 60 | 441 |
I added some months to make it easier for me to debug the measure
here is the measure:
Help when you know. Ask when you don't!
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 |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |