cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## 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.

1 ACCEPTED SOLUTION
Community Support

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:

Best Regards

Lucien

7 REPLIES 7
New Member

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

Community Support

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 :

Best Regards

Lucien

Post Prodigy

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.

Community Support

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:

Best Regards

Lucien

Frequent Visitor

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?

Post Prodigy

@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)

Resolver IV

Did you try removing the field after you did the sort by, and add it back in?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.