Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a matrix visual where I have the date in the columns. Is there a way I can sort descending so I have the most recent dates on the left vs. having to scroll (July should show on left first but right now it shows April ). I tried to go to the data tab and use the "Sort by" but the visual does not change. Any help is appreciated. See picture.
Solved! Go to Solution.
Hi @jcastr02 ,
Adjust to the below and sort by rank2:
RANK = var q1=FORMAT('Table'[Date],"YYYYMM") var q2=FORMAT(TODAY(),"YYYYMM") var value1=q1-q2 return value1
rank2 = var q1=FORMAT('Table'[Date],"YYYYMM") var q2=FORMAT(TODAY(),"YYYYMM") return if(q1-q2<0,ABS(q1-q2)+MAXX('Table','Table'[RANK]),q1-q2)
Or rank 2 use the below :
rank2 = var q1=FORMAT('Table'[Date],"YYYYMM") var q2=FORMAT(TODAY(),"YYYYMM") return if(q1-q2<0,MAXX('Table','Table'[RANK])+ABS(MINX('Table','Table'[RANK]))+(q1-q2),q1-q2)
Output:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
I found other way using a date field called s_AcceptedWeek (date field)
Using power query add the following steps:
Replying to this despite it's age b/c it was the first search result when I searched for how to sort matrix date column descending.
The way I resolved this is just to put the date field as a value then sorting off that via the three dots (...).
1. select the elipsis (...)
2. sort by date field (it will probably be summarized by earliest, this worked for me if it doesnt for you I'd play around with it or remove the summarization)
3. sort descending
4. go to Format Visual
5. specific column
6. white out the font for that value, total included if need be.
Hope this helps
Hi @jcastr02 ,
Before:
After:
Test the below :
Base on table ,create a new column:
RANK = IF(FORMAT('Table'[Date],"mmmm")=FORMAT(TODAY(),"MMMM"),1,2)
Then sort month by rank:
Final create visual :
You could download my pbix file to learn more details.
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi @v-luwang-msft Thanks for your help. I looked on your Power BI, the most recent month comes forward, which is great, but then the rest of the months are not sequential. See screen shot below from your BI.
Hi @jcastr02 ,
Adjust to the below and sort by rank2:
RANK = var q1=FORMAT('Table'[Date],"YYYYMM") var q2=FORMAT(TODAY(),"YYYYMM") var value1=q1-q2 return value1
rank2 = var q1=FORMAT('Table'[Date],"YYYYMM") var q2=FORMAT(TODAY(),"YYYYMM") return if(q1-q2<0,ABS(q1-q2)+MAXX('Table','Table'[RANK]),q1-q2)
Or rank 2 use the below :
rank2 = var q1=FORMAT('Table'[Date],"YYYYMM") var q2=FORMAT(TODAY(),"YYYYMM") return if(q1-q2<0,MAXX('Table','Table'[RANK])+ABS(MINX('Table','Table'[RANK]))+(q1-q2),q1-q2)
Output:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
This isnt working for me because of the circular dependancy; referencing the Date column in the calculated column which you are then going to tell Date to sort by. how did you get around that?
@James-Harpin I did try that but no luck. When I go on the matrix, the only options are to sort by what I have in Rows Or by what I have in Values, but not date (and that's in columns)
Did you try removing the field after you did the sort by, and add it back in?
User | Count |
---|---|
76 | |
75 | |
46 | |
31 | |
27 |
User | Count |
---|---|
99 | |
89 | |
52 | |
48 | |
46 |