Forum Discussion
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 | August | 12 |
| 2021 | September | 1 |
| 2021 | October | 2 |
| 2021 | November | 3 |
| 2021 | December | 4 |
| 2021 | January | 5 |
| 2021 | February | 6 |
| 2021 | March | 7 |
| 2021 | April | 8 |
| 2021 | May | 9 |
| 2021 | June | 10 |
| 2021 | July | 11 |
| 2021 | August | 12 |
| 2022 | September | 1 |
| 2022 | October | 2 |
| 2022 | November | 3 |
| 2022 | December | 4 |
| 2022 | January | 5 |
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!
4 Replies
Richard_100Resolver I
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 helpsRegardsRichard- ValtteriN
Community Champion
Hi,
You can sort columns by pressing the "sort by column" as seen on this picture:
You can e.g. sort your Year and Month Columns based on date.
For visuals you can sort data py pressing the ...
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up! 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!
