Sort Matrix by Year (descending)

[robincard]

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   2   3   4   3   1   1   2   3   4   3   2   1   2   3   4   3

Pears   2   1   1   2   2       1   2   2   3       2   2           1   2

My question is, how do I sort it by year (not month) so that 2019 comes before 2018?

The dates are a hierarchy from a CALENDAR function like this:

ReportDates = CALENDAR(DATE(YEAR(NOW())-1,1,1),DATE(YEAR(NOW()),12,31))

If I cannot sort the matrix can I sort the ReportDates?

[v-alq-msft · Accepted solution]

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.

[robincard]

Hi,

Thanks for your help @v-alq-msft . The Rank column looks good but I cannot use it to sort the ReportDates[Date] column as it is calculated from that column. It says:

"This column can't be sorted by a column that is already sorted, directly or indirectly, by this column."

I can add Rank to the matrix columns which gives the desired result, it's just unfortunate that Rank is displayed on the matrix.

[v-alq-msft]

Hi, @robincard 

When you create the calculated column, you need to put it in the matrix for ranking. Have you tried the workaround? As I said before, you may create a conditional column called 'Custom' in the Query Editor and make 'Year' column sorted by 'Custom' column.

Best Regards

Allan

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

[Tahreem24]

@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]

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]

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

[robincard]

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]

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.

[OwaisAkber]

LOL. It was so simple

[Ena]

So simple indeed. Lol.