Forum Discussion
Helper IIIRolling 12 months Average in Month-year format
Hi,
I have created a measure of rollling average for 6 months using quick measure. But it coming in the date format or in months format.
I wanted it in the month year format. For example, Jan-20, Feb-20 etc. For this I have created a column using the date column but when I use this month-year column it was not showing the result.
Can anyone help me with this ?
Thanks & Regards,
Kamal
4 Replies
- amitchandak
Super User
kamalbandam , You should be able to create column and sort column for month year and use that
Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")You should use date table for that
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
In case you want to select on date of month and want to show more data, refer
kamalbandamamitchandak ,
No, it is not working. I have done it but when I implement it. it is showing only 6 months and not showing full year with 6 months average.
- Jihwan_Kim
Hi, kamalbandam
Please correct me if I wrongly understood your question.
In my opinion, it is better to have Dim-Calendar-Table that contains Mon-Year-Column with SortOrder-Column.
These additional columns, and Dim Calendar Table, are easy to create in Power Query Editor.
Please check the below picture and the sample pbix file's link down below.
https://www.dropbox.com/s/irj3zoymjue1n0r/kamalbandam.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
- v-janeyg-msft
Community Support
Hi, kamalbandam
Here is a similar case you can check:Solved: Rolling 12 months average - Microsoft Power BI Community Hope it helps.
If it doesn't solve your problem, Can you share some sample fake data and your desired result? Because quick measure isn't suitable for complex context.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
