Problem with X Asix Sorting Month and Year

[Anonymous]

Hey guys, i have the following problem:

I have a dataset with a date column with values beginning from 2021-9 to today (formatted as date).

Now when i create a visual with the date on the x - Axis it gets sorted like that

Jan - September - Oktober-November December

But what i need is:

September - Oktober - November - December - January Because (its 2022)

I tried it with Concatenate and formatting it as a whole number and then sort it by that doesnt work.

Any suggestions?

Thanks in advance. I did not find a solution that work for me via the search function.

BR

[v-yetao1-msft · Accepted solution]

Hi @Anonymous 

Change the format of your date to (yyyy-mm) and add the date in column chart as a hierarchy .

The final result is as shown :

Best Regard

Community Support Team _ Ailsa Tao

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

[Anonymous]

Hi ValtteriN,

thanks for your advise. It definetly helped me but now my x axis looks like this:

If i sort the X-Axis it doesnt help at all.

Any suggestions?

[Anonymous]

the column seems to be in a text format. When i change it to date it sometimes messes up the year and tooks for every row 2022.

[ValtteriN]

Hi,

You can add additonal column with number type data and use that to sort your axis. e.g.

sort = CONCATENATE(YEAR('Calendar'[Date]),MONTH('Calendar'[Date]))

[Anonymous]

Ok ty! this worked, but now i have on the X-Axis the Sort Value like this:

But for the Dashboard viewers it would be better to have the CustomColumn on the X-Axis. But how can i define X=CustomColumn but sort it with the Sort column?

BTW. The mistake i did was that there was the zero missing for january "01" so i wasnt sorted correctly before by number

[ValtteriN]

Hi,

For these type of visualizations I highly suggest using a format that display year and month at the same time. Here is one way to do this in a calendar table: 

CustomFormat = FORMAT(DATE(YEAR('Calendar'[Date]),MONTH('Calendar'[Date]),1),"MMM-YY")

After you have this kind of column you can sort it e.g. using a column with data like this 2021,12
This will solve your sorting issue and it will make the visualization more readable.

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!