Forum Discussion

kamalbandam's avatar
kamalbandam
Icon for Helper III rankHelper III
5 years ago

Rolling 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

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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 

    https://www.youtube.com/watch?v=44fGGmg9fHI

    • kamalbandam's avatar
      kamalbandam
      Icon for Helper III rankHelper III
      5 years ago

      amitchandak ,

      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's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.