Sorting columns behavior in matrix not working

[cathryn13]

Hello All

I'm beating my head against wall on this.  I have a matrix that I'm trying to sort my columns by dates desc.  If I use just the date iteself, it sorts correctly descending.  But, if I try to add a hierarchy or use both the Year and date for the colums so you can drill down from year and have both the year and date showing, the year shows ascending and the dates show descending.  

Here are the things I've tried so far:

I created an index on the date table in descending order so 1 is the latest date with 10 being the oldest to try and sort Desc , clicked on the date field and chose sort by Index. 

I created a hierchy for Year and Week End Date and tried going that route vs adding the 2 columns separately. 

If I add the SortOrderDesc field as the first field in the columns, it sorts correctly, as soon as I remove it, it shows Year Asc, date, desc

I've tried multiple different scenarios and I can't seem to figure out how to solve this.

My table:

Shipment Year Shipment Week End Date   DateKey    SortOrderDesc  (Index created in Power BI)
2022                2022-01-15                         20220115           6                     1
2022                2022-01-08                         20220108           7                     2
2022                2022-01-01                         20220101           8                     3
2021                2021-12-25                         20211225           9                     4
2021                2021-12-18                         20211218          10                    5               

I'm sure I'm missing something obvious.  

Has anyone else had this issue?

[Sivakumar_H]

Hi @cathryn13 

Try to create two seperete sort columns for Year and Weekend date and Sort Year to Year sort column and Weekend Date to Weekend Date Sort column. This should work and we can order any type ascending or descending to the columns which we required.

Say for example we want hierachy order as A-P1,A-P3,A-P2 for all the parent values then we can create sort by column according to that required order.

Source

Output

[cathryn13 · Accepted solution]

Just in case anyone has this issue, I found a solution.  This may not be the most optimal but it works...

I wrote my SQL code to have 2 Rank() Over functions, one that ranks the date column in Desc order and a 2nd that Ranks the Year in Desc Order.

I'm sure you could create the same columns in Power BI, I'm just more comfortable, writing SQL vs DAX