Forum Discussion
Creating a Rolling 12 months using Month Column
Hi Community, I have to create measure for calculating a rolling 12 months for Sales. I don't have date column but i have month colum(like 1,2,3 ..12) . How can i acheive it. Please help. ...
- I have created a calender table and created a column order using ranx function. below is the working DAX.12M Rolling YTD = IF (HASONEVALUE ( 'Calendar'[YearMonthNo] ),CALCULATE (SUM( 'NA Daily'[Total Sales] ),FILTER (ALL ( 'NA Daily'[Order] ),'NA Daily'[Order]>= SELECTEDVALUE ( 'Calendar'[Order_Calendar] ) - 11&&'NA Daily'[Order] <= SELECTEDVALUE ( 'Calendar'[Order_Calendar] ))),CALCULATE ( ( 'NA Daily'[Total Sales YTD] ) ))
Hi Anonymous
You may have a look at below posts. Sample data and expected output will be helpful to provide an accurate solution.
https://community.powerbi.com/t5/Desktop/Moving-Average-for-Prev-6-Next-6-Months/td-p/171712
https://community.powerbi.com/t5/Desktop/Averages-For-The-Last-12-Months/td-p/324289
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/
Regards,
Cherie
It doesn't helps me out.
If you have Year and month, you can use the function 'DATE'
Date = DATE(Tabel3[Year];[Month];1)When you have your date column, you can use this link for the rest
https://community.powerbi.com/t5/Desktop/Trailing-12-or-Rolling-12-month-sum/td-p/164419Hi Anonymous
You may try with below measure. Attached the sample file. If it is not your case, please share some sample data.
Measure = CALCULATE ( SUM ( Data[Sales] ), FILTER ( ALL ( 'Data' ), 'Data'[YearMonth] >= MAX ( Data[YearMonth] ) - 11 && 'Data'[YearMonth] <= MAX ( 'Data'[YearMonth] ) ) )Regards,
Cherie
- I have created a calender table and created a column order using ranx function. below is the working DAX.12M Rolling YTD = IF (HASONEVALUE ( 'Calendar'[YearMonthNo] ),CALCULATE (SUM( 'NA Daily'[Total Sales] ),FILTER (ALL ( 'NA Daily'[Order] ),'NA Daily'[Order]>= SELECTEDVALUE ( 'Calendar'[Order_Calendar] ) - 11&&'NA Daily'[Order] <= SELECTEDVALUE ( 'Calendar'[Order_Calendar] ))),CALCULATE ( ( 'NA Daily'[Total Sales YTD] ) ))
