Forum Discussion
Sort by columns in a matrix
Hello, I have a matrix in the rows, I have the names of the stations, in the columns I have the dates and in the values I have the quantity that is sold for each day. I would like to be able to order from highest to lowest on any date that I can sort by rows and by total quantity, but not by columns, they know how to do it.
Hi Anonymous ,
If the date column is from your data table, you could create a custom column in Power Query, like below.
= Date.Year([Date])*10000 + (12-Date.Month([Date]))*100 + 31-Date.Day([Date])If the date column is from the calendar table that you create with DAX, you could try like this:
Date = VAR vCalendar = CALENDAR ( MIN ( 'Table_Calendar'[Date] ), MAX ( 'Table_Calendar'[Date] ) ) RETURN ADDCOLUMNS ( vCalendar, "Index", YEAR ( [Date] )*10000 + (12 - MONTH ( [Date] ))*100 + 31 - DAY ( [Date] ) )After creating the new column, select the date column and sort by the new one.
For more details, please see the attachment.
3 Replies
Create a desc rank and make it as sort for date and try
New Date = date
Rank =Rankx(all(date),Date[date],,desc,desnse)
Hi Anonymous ,
If the date column is from your data table, you could create a custom column in Power Query, like below.
= Date.Year([Date])*10000 + (12-Date.Month([Date]))*100 + 31-Date.Day([Date])If the date column is from the calendar table that you create with DAX, you could try like this:
Date = VAR vCalendar = CALENDAR ( MIN ( 'Table_Calendar'[Date] ), MAX ( 'Table_Calendar'[Date] ) ) RETURN ADDCOLUMNS ( vCalendar, "Index", YEAR ( [Date] )*10000 + (12 - MONTH ( [Date] ))*100 + 31 - DAY ( [Date] ) )After creating the new column, select the date column and sort by the new one.
For more details, please see the attachment.
