Forum Discussion

robincard's avatar
robincard
Frequent Visitor
6 years ago
Solved

Sort Matrix by Year (descending)

Hi,   I have a matrix that looks like this:   Year   2018                                            2019        Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Apples  1   ...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, robincard 

     

    Based on your description, you can create a calculated column as follows.

    Rank = RANKX('Table','Table'[Year],,DESC,Dense)

     

     

    You may go to ‘Modeling’ ribbon, click ‘Sort by Column’, select ‘Rank’ column.

     

    Result:

     

    As a  workaround, you can also click ‘Edit Query’, go to Query Editor, choose ‘Add column’ ribbon, select ‘Conditional Column’.

     

    You may configure as follows, then go to ‘File’ ribbon, click ‘Close and Apply’.

     

    Then you can make ‘Year’ column selected, go to ‘Modeling’ ribbon, click ‘Sort by Column’, select ‘Custom’ column. Here is the result.

     

    If I misunderstand your thought, please show me your sample data and expected output. Do mask sensitive data before uploading. I am glad to solve the problem for you.

     

    Best Regards,

    Allan

     

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

Tahreem24's avatar
Tahreem24
Icon for Super User rankSuper User
6 years ago

robincard ,

 

You need to create index column for Date Column and use the Sort by COlumn option to sort your Date according to Index. For better understanding follow the beloe step:

1. Go to Query Editor --> Select your ReportDate Column --> Add Column Menu --> Select Index Column --> Apply and close. SO it will create index column.

2.  GO to Data Tab --> Select ReportDate Column --> Modelling Menu --> Sort By COlumn and Select Index.

3. Your matrix data is sorted accordingly.

 

Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

 

 

robincard's avatar
robincard
Frequent Visitor
6 years ago

Hi,

 

Thanks for your help Tahreem24 . I'm sorry, this does not work as the ReportDates is a DAX function:

 

ReportDates = CALENDAR(DATE(YEAR(NOW())-1,1,1),DATE(YEAR(NOW()),12,31))
 
and cannot be edited in Query Editor.
  • Ena's avatar
    Ena
    Frequent Visitor
    3 years ago

    Hi robincard , did this ever get resolved? I have run into the same issue and can't seem to find a solution.

    • robincard's avatar
      robincard
      Frequent Visitor
      3 years ago

      Hi, I actually remade this one as a paginated report where I could sort it by year desc and month asc.

       

      I think it's also possible to create a "Month Year" column on the date table using FORMAT([Date], "MMM-YY") and then add a "Sort Order" column which uses -((year([Date]) * 100+month([Date])) and sort by that column to get them in reverse order. So Jun-23 is -202306. Then use "Month Year" on the matrix, but you lose the hierarchy.

      • Ena's avatar
        Ena
        Frequent Visitor
        3 years ago

        Right after I posted my comment, I found the solution!!! It worked for me. https://www.youtube.com/watch?v=EVex9Gv-Hok

         

        The trick is to make sure you have selected "year" (1:30) in the Report view before you go back to the Data view to sort. I did not lose hierarchy.