Forum Discussion

jcastr02's avatar
jcastr02
Icon for Post Prodigy rankPost 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

v-luwang-msft's avatar
v-luwang-msft
Icon for Community Support rankCommunity 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

  • jcastr02's avatar
    jcastr02
    Icon for Post Prodigy rankPost 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's avatar
      v-luwang-msft
      Icon for Community Support rankCommunity 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

      • bondonian's avatar
        bondonian
        Frequent Visitor
        2 years ago

        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?