Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
4 years ago
Solved

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...
Need Help
  • v-luwang-msft's avatar
    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

g170479's avatar
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.