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 ,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I must have missed something in your explanation as you are using the data I sent up. I followed, I thought, the instructions you provided and am still getting the results in a non-month order. 

    1. Created the new calendar table

    2. Sorted it by the yearmonth column

    3. Created a relationship from the new calendar table to the data table

    4. Added a new visual

    5. Put the category in the row section, the month from the new calendar table into the column field, and tickets (Count) into the values. 

    It resulted in the order of Dec, Jan, Nov for the data. Can you post the PBIX so I can see what I didn't do correctly?

    Also, "But if you have duplicated month value in your fact table, you really need to put "year" in the column." do you mean that if I did this as an 18-month look back that the year would need to be included in the columns field?

     

    Thanks