Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling 12M Date Order based on Selection

Hi,

Im a new user to Power BI and have been trying to design my reports.

I have looked up previous Rolling 12 Month questions and was able to source the below Dax statement

Dax Formula for Previous 12 MonthsGraph outcome

My final hurdle is that iIm trying to get in to sort in order of last 12 Month. That image is filtered on 30/9/19 I would like it to be far right and then go backwards eg Aug 19, July 19, Jun19... to Oct18.

Adding report time to Column Series I was able to split the years but no further.

Any help would be greatly appreciated. 

Thanks in advance.

Eamon

Need Help
  • danextian's avatar
    danextian
    6 years ago

    Hi Anonymous 
    Did you want to show the period as "MMM YY"? If so, you can create a custom column for that 

    Month and Year = 
FORMAT ( Table[Date], "MMM YY" )
 
Month and Year Sort= 
FORMAT ( Table[Date], "YYYYMM" )

    The second formula above is for sorting the first formula. Once you have created these calculated columns, select Month and Year, go to Column Tools and Sort by Month and Year Sort. 

5 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    After several testing, I realized that there was no way to sort the period chronologically if i just use month as the axis. The workaround is to include both year and month from the date hierarchy then sort the visual by YearMonth. Turn off concatenate labels. The Years will still be visible but you may cover it with a shape then just group with the bar chart.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi danextian,

       

      Thanks for the quick reply. I have turned off concatenate labels and have the date hireachy detup with Year and month but still shows in the same format unfortunatley. I'll keep playing around with it.

       

      Are there any other simpler formuls that would just simply show last 12 months absed on date/month selection? 

       

      Thanks

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        6 years ago

        Hi Anonymous 
        Did you want to show the period as "MMM YY"? If so, you can create a custom column for that 

        Month and Year = 
FORMAT ( Table[Date], "MMM YY" )
 
Month and Year Sort= 
FORMAT ( Table[Date], "YYYYMM" )

        The second formula above is for sorting the first formula. Once you have created these calculated columns, select Month and Year, go to Column Tools and Sort by Month and Year Sort.