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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks - yeah, I have done some advanced things in the past, but I still have gaps in my knowledge you can drive a semi-truck through. I appreciate the more detailed answer so I could see your thought process and how it applied to the post you linked.

 

I tried that and didn't have any luck, I think it's because I'm using a matrix visualization as opposed to a table or other visual - they seem to react differently.

 

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