Sorting months in chronological order

[gjayne84]

Hi everyone

I know this question has been asked a lot but I'm really struggling getting a line cgart to displa the months in chronological order rather than alphabetical.  I have followed the instructions here to create a MonthNo column.  However, I can't get the column to display the month numbers with a leading 0 so the sort order is incorrect as shown below:

If anyone could give me any pointers, or whether there is a better way to achieve what I need, I would be really grateful.

Many thanks in advance for your help.

[jjupiter100]

I think I have a much simpler answer.  The 2 main issues are the first letters of each month of the year are not in alphabetical order, so Excel always wants to put April first instead of January.  And if you try to give each month a number, October, November, and December present problems because 10, 11, and 12 get put before 1 (which would be January).

So, however you're going to label your months (typically January = 1, February = 2 and so on), make October = 91 (because September would have equaled 9), make November = 92 and December = 93. 

This gets your pivot table sorted correctly and when you go to make your graph, you can just rename each of those fields to be whatever you want.  And most importantly, they'll stay in the same order you want them.

[PattemManohar · Accepted solution]

@gjayne84 Please try to change the datatype of MonthNo field to Numeric. Then use "Sort by Column" option to sort the Month by MonthNo (Select Month Field and then use "Sort by Column" option to use MonthNo as sort order)

[Stachu]

you need to sort Month by MonthNo with 'Sort By Column' in Modelling ribbon (you may need to remove and add the Month in the visual to remove other sorting)

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column#sort-using-the-sort-by-column-butto...

[Anonymous]

Hi @gjayne84.

You can add a 0 before the number from 1 to 9 to get the correct order doing a simple transformation in Query Editor. 

Text.PadStart([MonthNo], 2, "0")

Regards,

Marcos Pérez