Matrix Sort by Column Header Date Descending

[a68tbird]

This is a popular topic on the boards, I know, and I've read many different solutions, however nothing seems to be working for me and it's got me stumped.

My matrix has a Date Hierachy for columns.  It's 8 years of columns, so I'd like the most recent year leftmost in the table.  I added a RANK() line to my SQL query:

RANK() over(order by Skus.EventDate DESC) as DateSort

 which gives the most recent date a value of 1, and so on.  Based on this column I then used the Sort By Column (sort EventDate by DateSort).  It all looks fine in the Data view (although - is there supposed to be a check mark beside the column selected as the sort by? Mine doesn't show a checkmark....)

Going back to look at my matrix, it hasn't done anything - old year is still leftmost.  What am I missing? 

[v-yalanwu-msft · Accepted solution]

Hi, @a68tbird ;

I tested it , product as rows, sales as values, date as  column;

If the column is Date Hierarchy, sort by column is not work ,so we should change the different column about year and month.

create a rank column.

Column =RANKX('financials',YEAR('financials'[Date]),,DESC,Dense)

The final output is shown below:

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

[a68tbird]

Everything that I'm reading, seems to suggest that this should work.  I decided to create a brand new report, new sample data, and try it again from scratch.  With my data imported, I created two calculated columns:

DateSort = sampleData[RecognitionDate].[Date]

Rank = RANKX(sampleData,sampleData[RecognitionDate],,,Skip)

I then sorted the DateSort date by Rank.  This displays correctly in the Data view (most recent sales date is ranked 1).  I then add three fields to a matrix: Products as Row, StoreTotal as Value and DateSort as Columns.  The matrix still has oldest year leftmost in the grid.  

I've followed a suggestion from this post: Solved: Sort date field in Matrix column header descending - Microsoft Power BI Community but it's not working for me.  Does the fact that I'm using a Date Hierachy in the matrix make a difference?  

[parry2k]

@a68tbird no, it doesn't show the checkmark but highlights the sorted by column and in your case, it looks ok.

Can you click on the top right corner 3 dots on the matrix visual and make sure the correct column is used for sorting.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[a68tbird]

EventDate doesn't show as an option there in the Sort By.  I thought that was the whole issue with the "sort by column headers".  By default PowerBI sorts the date by ASC, and columns don't appear in the matrix visual sort by options.