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 friends,
I am trying to create Rolling 13 months sum using DAX and I am looking for some help around it.
This is my data set. And I want to create a measure and not a column which gives me Rolling 13 months sale for the month I select in the filter.
Data Set:
Month Year | Sale |
Apr-21 | 1000 |
May-21 | 200 |
Jun-21 | 3000 |
Jul-21 | 1200 |
Aug-21 | 1500 |
Sep-21 | 1600 |
Oct-21 | 1300 |
Nov-21 | 2000 |
Dec-21 | 1000 |
Jan-22 | 2000 |
Feb-22 | 3000 |
Mar-22 | 1000 |
Apr-22 | 1000 |
May-22 | 2000 |
Jun-22 | 1000 |
Result Expected:
Month Year | Rolling 13 Month Sale |
Apr-22 | 19800 |
May-22 | 20800 |
Jun-22 | 21600 |
Thanks
Solved! Go to Solution.
Hi @Anonymous ,
This measure below will work if you have a date column in your table. It won't work if you have only the month name as it is a text.
Measure =
CALCULATE(
SUM( 'Table'[Sale] ),
DATESINPERIOD( 'Table'[DateRef], MAX( 'Table'[DateRef] ), -13, MONTH )
)
Proud to be a Super User!
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks Ashish for your help and time.
You are welcome.
Hi @Anonymous ,
This measure below will work if you have a date column in your table. It won't work if you have only the month name as it is a text.
Measure =
CALCULATE(
SUM( 'Table'[Sale] ),
DATESINPERIOD( 'Table'[DateRef], MAX( 'Table'[DateRef] ), -13, MONTH )
)
Proud to be a Super User!
Hi mate, thans for your time and help, it worked.
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |