date column sorting in a matrix from latest to oldest

[Anonymous]

I want to sort the date columns from march2018 to january2018(i.e in desc order), while now it is in ascending order(jan to mar).

Regards,

Soumya

[amitchandak · Accepted solution]

@Anonymous , This for matrix column where you do no have sort option

Create a new column like

Month Year = FORMAT([Date],"mmmm-yyyy") // New column because it will always sort desc .
Month Year sort = FORMAT([Date],"yyyymm")
Month Rank = RANKX(ALL('Date'),'Date'[Month Year Sort],,DESC,Dense) // Create desc rank on month sort column or Month Start date

Now sort Month Year  on Month Rank  , this will sort desc by default

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[v-easonf-msft · Accepted solution]

Hi， @Anonymous 

You can try to enter a table with two columns as below,then make the column "Month_year" sort by index:

Then  you need to create another column "column" to format the date to "mmmm yyyy" in the raw table

Column = FORMAT(Table1[Date],"mmmm yyyy")

so that you can build relationship between two  tables:

After that ,you can create  a Matrix table with a column headers  in a custom order.

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Please refer this existing thread for similar issue:

https://community.powerbi.com/t5/Desktop/Sort-Date-Column-in-Matrix/td-p/602217

The above thread suggests using a ranking method based on the following blog:

https://www.excelguru.ca/blog/2018/06/14/ranking-method-choices-in-power-query/

Thanks,

Pragati

[amitchandak · Accepted solution]

@Anonymous , This for matrix column where you do no have sort option

Create a new column like

Month Year = FORMAT([Date],"mmmm-yyyy") // New column because it will always sort desc .
Month Year sort = FORMAT([Date],"yyyymm")
Month Rank = RANKX(ALL('Date'),'Date'[Month Year Sort],,DESC,Dense) // Create desc rank on month sort column or Month Start date

Now sort Month Year  on Month Rank  , this will sort desc by default

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[Anonymous]

Thanks Amit  for your reply,

But the column in the matrix is still from oldest date and june2020 is at the end near the total value. while jan2018 is at the beginning.

My source is a sql query, I even tried to add a column with rank of month and tried to sort the column, but the matrix is not changing its order.

Regards

[v-easonf-msft · Accepted solution]

Hi， @Anonymous 

You can try to enter a table with two columns as below,then make the column "Month_year" sort by index:

Then  you need to create another column "column" to format the date to "mmmm yyyy" in the raw table

Column = FORMAT(Table1[Date],"mmmm yyyy")

so that you can build relationship between two  tables:

After that ,you can create  a Matrix table with a column headers  in a custom order.

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is working if I Have month-year format, but when I have only year in column, it is not working. 

Still thanks for your help.