Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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)
@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
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)
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.
@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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |