Forum Discussion
Descending Order when using date in a matrix
In a matrix, I need the numbers to be ordered from the highest to the lowest, that the current day is always the first from left to right. how can I do this?
I'm using a calendar table relating to the original date
Today is like that:
Hi CastroRibeiro
You can achieve it with 2 manipulations on your date table:
1. Duplicate "Day" column:day_For_matrix = day('date'[Date])2. Add a column for Descending sorting:
Rank_Order = RANKX(FILTER(ALL('date'), MONTH('date'[Day]) = MONTH('date'[Date])), 'date'[Day], , DESC, Dense)Sort your new day column by this ranking :
Result:
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
3 Replies
Hi CastroRibeiro
You can achieve it with 2 manipulations on your date table:
1. Duplicate "Day" column:day_For_matrix = day('date'[Date])2. Add a column for Descending sorting:
Rank_Order = RANKX(FILTER(ALL('date'), MONTH('date'[Day]) = MONTH('date'[Date])), 'date'[Day], , DESC, Dense)Sort your new day column by this ranking :
Result:
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi Rita!
I just ran into this problem today and found out that your solution is the best one published online
So I had an urge to improve it 🙂
Issue: your solution orders days in descending order but keeps months and years in ascending orderOffered solution: Add the following custom column in the Edit Query screen (M Language), define it as "Whole number" and then sort it in the desired order (steps 2 & 3 on your example):
- Duration.Days([DateField] - #datetime(1970, 1, 1, 0, 0, 0))*86400
Note the dash - minus sign is significant! it creates a Unix timestamp, simply a date stored as an integer, making it easy to order.p.s
your contribution is amazing and I joined some of your groups 🙂
Thank you so much for the kind words, I’m really excited to hear that!😊
Regarding the excellent professional comment - you're absolutely right, and of course, this logic can be implemented at any level of granularity required. I think I’ve already prepared about 5 different variations of it floating around here in the forum.
As for the groups, I'm glad to hear that! I hope you manage well with the content being mainly in Hebrew. Aside from what’s written here and on the blog, that's the main language I operate in.
Thank you again, and have a wonderful day!
