Descending Order for Date in Matrix

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.

Need Help

v-luwang-msft

4 years ago

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

Descending Order for Date in Matrix2.pbix26 KB

8 Replies

James-Harpin
Resolver IV
4 years ago
jcastr02
Did you try removing the field after you did the sort by, and add it back in?
jcastr02
Post Prodigy
4 years ago
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)
v-luwang-msft
Community Support
4 years ago
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
Descending Order for Date in Matrix.pbix24 KB
- jcastr02
  Post Prodigy
  4 years ago
  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.
  - v-luwang-msft
    Community Support
    4 years ago
    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
    Descending Order for Date in Matrix2.pbix26 KB
Materous
New Member
2 years ago
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
g170479
New Member
1 year ago
I found other way using a date field called s_AcceptedWeek (date field)

Using power query add the following steps:
= Table.AddColumn(
#"Renamed Columns", //Previous step name
"s_AcceptedWeek_Sort", //Column name
each
if [s_AcceptedWeek] = null then //If the Accepted Week is null then do nothing (default null)
null
else //If there is a value, then proceed and calculate the value
-1 * (Date.Year([s_AcceptedWeek]) * 10000 + Date.Month([s_AcceptedWeek]) * 100 + Date.WeekOfYear([s_AcceptedWeek]))
),

#"Changed Type s_WeeklyAccepted_Sort" = Table.TransformColumnTypes(
#"Added s_WeeklyAccepted_Sort Column", //Previous step name
{
{"s_AcceptedWeek_Sort", type number} //Convert to Numeric value
}
)

After this code, then change the sort of your date field to be the custom field created.