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

sevenhills's avatar
sevenhills
Super User
5 years ago

give me two minutes, my explanation looks wrong ... will update by looking my pbix file

sevenhills's avatar
sevenhills
Super User
5 years ago

Hi Anonymous ,  I updated my original reply above, please check

  • sevenhills's avatar
    sevenhills
    Super User
    5 years ago

    Hi Anonymous 

     

    I download your PBIX file.

     

    a) Modified your calendar table as

    Calendar = ADDCOLUMNS ( CALENDARAUTO (),
    "Year", FORMAT([Date], "yyyy"), 
    "MonthNo", MONTH([Date]), 
    "Month", FORMAT([Date],"MMM"),
    "Quarter", FORMAT([Date],"\QQ"),
    "YearMonth", FORMAT([Date],"YYYY-MM"), 
    "WeekdayNo", WEEKDAY([Date],2), //1-Sun..Sat, 2-Mon..Sat
    "Weekday", FORMAT([Date],"ddd"), 
    "WeekNo", WEEKNUM([Date], 2),
    "Month - 2", [Date]
 )

    b) Select "Month - 2" and clicked Sort by Column as "YearMonth"

     

    c) matrix modified to use "Month -2" and it works!

     

    Thanks