Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Sort by Year and Month

Hi All,   I have a table mentioned as below, I would like to sort it based on year and Month Number.  how can i achieve it in DAX query or Powe BI visual Year Month Month Number 2020 Augu...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous，

     

    Please try this column.

     

     

    Rank = 
RANKX(
    'Table',
    RANKX('Table','Table'[Year],,0)
    +DIVIDE(RANKX('Table','Table'[MonthNumber],,1),COUNTROWS('Table')+1)
)

     

     

    The result should be like this.

     

    Best Regards,

    Community Support Team _ Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

     

Richard_100's avatar
Richard_100
Icon for Resolver I rankResolver I
4 years ago

Hello

 

My preference is usually to use calculated columns on the raw table to produce columns that can be sortable in one go, or dropped onto graph axes with no worries:

 

 

A text formatted version that can be sorted A-Z:

Sort Option 1 = 'Table'[Year] & " " & FORMAT('Table'[Month Number], "00")
 
A date formatted version, basically creating a date field with the 1st of the month, to be sorted oldest-to-newest:
Sort Option 2 = DATE('Table'[Year],
SWITCH([Month], "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August",8, "September", 9, "October", 10, "November", 11, "December", 12)
, 1)
 
Hope that helps
 
Regards
Richard