Power BI Matrix Date Column Sorting

[andregrgr]

I have a table working for me. Where I have one category in my rows and month-year in my columns. I want to be able to sort my columns by date, soonest to latest but They are sorting alphabetically, the only sorting options I have under more options in the visual are for my columns. 

Any help is greatly appreciated. Thank you!

[Ritaf1983 · Accepted solution]

Hi @andregrgr 

You need to add a calculated column for sorting order, with the Dax formula :

sort_month_yar = financials[Year]*10000+financials[Month Number]

after this modify the sort order of month year according to this formula from the table view:

result:

Pbix is attached

more information about sorting by another column:

https://www.youtube.com/watch?v=x1tmabi1beA

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

[Nikhil_567]

Hi @Ritaf1983 ,

Thanks for your approach.

What if I need to sort the months in descending order?

Like Jan 2024, Dec 2023, Nov 2023 and so on.

[Ritaf1983]

Hi @Nikhil_567 
Then you need a calculated column with this logic to sort by :
sort_month_yar = -(financials[Year]*10000+financials[Month Number])
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Mohanad-Mustafa]

Hi @Ritaf1983 , really need your kind assistance, I did what you said my dates are still showing the old year like 2023 and then the new year like 2024 on my Matrix visual, any suggestion as why is that? I have followed all the steps you mentioned by adding the - sign before the measure 

[Ritaf1983]

Hi @Mohanad-Mustafa  if it isn't working, something is not as I explained.
Please share the link to the pbix and I will try to help.

[Nikhil_567]

Perfect, worked smoothly!!
Many thanks

[Ritaf1983]

Happy to help 🙂

please consider Accepting  the solution to help the other members find it more quickly

[Nikhil_567]

@Ritaf1983 In the same visual I need to show months as well (from another date column) on the rows of the matrix, like below-

How can I sort the rows in the same way?

[Ritaf1983]

Hi @Nikhil_567 
You can sort the column that is connected to rows on the matrix by the same "order column" that you added. 
There is no limit to how many columns can be ordered by this column.
please consider Accepting  the solution to help the other members find it more quickly

[Nikhil_567]

@Ritaf1983 I tried doing the same however getting the error-

[Ritaf1983]

Hi @Nikhil_567 

This is happening because the dates are likely conflicting with each other's sorting.

Option 1 for a solution:

• Create an additional column for sorting the second column (less optimal).

Option 2, which is more efficient:

• Create 2 tables of dates with the required columns, including those intended for sorting.
• Join them to a fact table.
• Use one table for rows and the other for columns in the matrix.

please refer to the linked lecture ( from 20 minute).
https://www.youtube.com/watch?v=tBr6IFmmvOE

if you need a guide to creating date tables it is here :

https://radacad.com/power-bi-date-or-calendar-table-best-method-dax-or-power-query

please consider Accepting  the solution to help the other members find it more quickly

[andregrgr]

worked like a charm. Thanks!

[Ritaf1983]

@andregrgr 
You're welcome 🙂

[Ritaf1983]

Hi @andregrgr 

You need to add a calculated column for sorting order, with the Dax formula :

sort_month_yar = financials[Year]*10000+financials[Month Number]

after this modify the sort order of month year according to this formula from the table view:

result:

Pbix is attached

more information about sorting by another column:

https://www.youtube.com/watch?v=x1tmabi1beA

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