Descending Order for Date in Matrix

[jcastr02]

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.

[g170479]

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. 

[Materous]

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

[v-luwang-msft]

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

[jcastr02]

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 · Accepted 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

[bondonian]

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?  

[jcastr02]

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

[James-Harpin]

@jcastr02 

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