Sorting Month Axis by chronological order

[ThomasSan]

Hi everyone,

I need help in ordering the y-axis in my visual by month in a chronological order

My order dates start in August 2023 and go until May 2024. Hence, I would like to have my months ordered in a chronolgoical order with August at the top and May at the bottom. As you can see in my screenshot, I have already drilled down from the year level to month level but, contrary to my expectations, the ordering of the months does not consider to which year each month belongs to. Does anyone therefore know what I can do to accomplish the desired result of having August at the top and May at the bottom?

[Anonymous · Accepted solution]

Hi @ThomasSan ,

Thanks @Joe_Barry  for the quick reply. I have some other thoughts to share:

1.We can create a new table.

Month	Number
2023-August	1
2023-September	2
2023-October	3
2023-November	4
2023-December	5
2024-January	6
2024-February	7
2024-March	8
2024-April	9
2024-May	10

2.We can create a [Year-Month] column in table.

Year-Month = YEAR([Date]) & "-" & FORMAT([Date],"mmmm")

3.We can create a model relationship.

4.Select the [Month] column to sort by number column.

5.Place the field as shown in the following image.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[ThomasSan]

hi @Anonymous 

thank you for your response! this was the solution! 🙂

[Joe_Barry]

Hi @ThomasSan 

In Power query sort the order date from Earliest to Latest. Create an index column, load the table and then sort the order date by the index columns

Joe

[ThomasSan]

Hi @Joe_Barry 

thank you for your reply. I tried the index column but I receive the follow message when I try to sort the order date column according to the index

I assume this pops up because I several entries have the same date but a different index value. Do you happen to know how to deal with this?

[Joe_Barry]

Hi @ThomasSan 

Ok delete the index and add this conditional column in Power Query  and Format the column into Whole Number

Text.From(Date.Year([Date]) ) & Text.From(Date.Month([Date]))

Sort by that column instead

But I'm thinking you may need to go down the Year/Month route in your visual.

Still do the above and in theTable view add a column then sort by the column above. You can adapt the formatting to suit your needs. I think without the context of the year, it won't be possible to achieve what you need, but I stand to be corrected

Year/Month = FORMAT(Incidents[DateUTC], "YY - MMM")

[ThomasSan]

Hi @Joe_Barry 
I get this message now

also, would this even be a viable solution as index 20241 (January 2024) is smaller than 202312 (December 2023)?