Matrix table decending date columns

[Bokchoy]

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 date. I have to drag the tool bar below the table everytime to get to the latest day.

Is there a simply way to sort the table with the latest date on the left?

Note: i read on a solution where you can sort date columns with a custom column. I dont want to do that becuase i use the same date columns to power other line graphs and i actually want the lastest dates on the right side.

[amitchandak]

@Bokchoy , Create two new column

Date1 =[Date]

Date Rank = rankx('Date', [Date],,desc,dense)

Sort Date1 on Date Rank1 and use Date1 in Matrix

refer if needed

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

[Bokchoy · Accepted solution]

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)

[v-rzhou-msft]

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]

@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 🙂