Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Month Sorting in a Matrix

Hello,   I have a set of data I want to view comparing results on a month-to-month basis for the last three months.   The rows are the various metrics, the columns are the month of the week-endin...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    If you don't have duplicated month value in your fact table, you can try the following steps:

     

    1. Create the calendar table:

    Calendar = ADDCOLUMNS(CALENDAR(DATE(2020,10,1),DATE(2021,1,31)),"Year",YEAR([Date]),"MonthNum",MONTH([Date]),"Month",FORMAT([Date],"mmm"),"YEARMONTH",YEAR([Date])*100+MONTH([Date]))

     

    2. Sort the month column by YEARMONTH column:

     

    Then it will show like you want:

     

    But if you have duplicated month value in your fact table, you really need to put year in the column.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
5 years ago

Hi Anonymous ,

 

Would you please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQMGgP4_WNBKlWouIb2JhpkBa6cEPFVUuiCpSId6x9ZTFQ?e=Oun1UX?

 

And if you have an 18-month look back , for example,2019.6 -2020.12, there are duplicated month value for jan, feb and so on. sort by column will result in error. And we need to use year column to distinguish them.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai