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

    I don't know what you are attempting to show here. I'm not having an issue with sorting by the month name unless it's your assertion that I create a measure that combines the 4 digit year preceding the month name.

     

    • sevenhills's avatar
      sevenhills
      Super User
      5 years ago

      Hi  Anonymous 

       

      Sorry, since you are already advanced user (than me), based on the profile, I did not go in details. 

       

      I had similar requirement in the past and want to present the data for matrix. so I did like below steps. (for some reasons, my profile do not allow me to upload pbix file)

       

      [I edited this original post reply]

       

       

      Columns

      • MM = Month('Calendar'[Date])
      • YYYYMM = Format('Calendar'[Date], "YYYYMM")
      • Month = Format('Calendar'[Date], "mmmm")
      • Month - 2 = 'Calendar'[Date]
      • YYYYMMDD = format('Calendar'[Date], "YYYYMMDD")

       

      Format the "Month - 2" as mmmm and sort the column using YYYYMM

       

      Note: It only works for matrix, which was my requirement too.

       

       

       

      The image shows all data, but typically we do rolling 12 months or (12+1) 13 months in matrix. 

       

      hint: I created as many format types to the needs in date/calendar table and use it. 🙂 

       

      Hope this helps!

       

      Thanks

       

      • 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.