Forum Discussion

Bokchoy's avatar
Bokchoy
Helper II
4 years ago
Solved

Matrix table decending date columns

Hi All,   i've got a pretty standard matrix table that shows sales by client over time. (as shwon below)   By default the date columns are sorted from left to right, right being the lastest...
Need Help
Show and Tell
  • Bokchoy's avatar
    Bokchoy
    4 years ago

    Hi amitchandak 
    thanks for helping me. I've followed your instructions and made the two columns, however it still doesnt seem to work. (See image)

    date_2 = vw_Calendar_BI[SingleDate]
    date_rank = RANKX(ALL(vw_Calendar_BI),vw_Calendar_BI[SingleDate].[Date],,DESC,Dense)

     

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Bokchoy 

Here I suggest you to sort your Year, Month and Day by Negative numbers(-Year, -Month and -Day).

Here I create a calendar table and relate it with data table.

calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Mon", FORMAT ( [Date], "MMM" ),
    "Day", DAY ( [Date] ),
    "Month", MONTH ( [Date] ),
    "SortMonth", - MONTH ( [Date] ),
    "SortDay", - DAY ( [Date] )
)

Use sort by function to sort [Mon] by [SortMonth] and sort [Day] by [SortDay].

Result is as below.

Here is a blog, you may refer to it for more details.

 

Best Regards,
Rico Zhou

 

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

Bokchoy's avatar
Bokchoy
Helper II
4 years ago

ricoo amitchandak 
Hi guys thanks for the help. I was able to get what I needed by creating individual columns for year, month, day from my original date column. It seems like the automatic date hierarchy overrides the custom sorting. 
I had to manually create a date hierarchy with the new columns and it worked fine 🙂