Not able to sort months chronologically

[manavmehta]

I am sorting my modified Month column (which is derived from modified date column) chronologically. I have data for April 2018 through February 2019. Although I created a separate column for Sort= FORMAT (Modified Date,"MM YYYY"), but when I am sorting my modified month based on Sort column, it gives me an error that "We can't sort Month column by Sort. There can't be more than one value in sort for the same value in Month." Please help what shall I do in such a scenerio.

[Anonymous · Accepted solution]

Hi @manavmehta ,

Yeah that order alphabetically and since there's more than one value to the same month PBI can't order it.

With this said you can create a table with "Enter Data" that would be like:

1	January
2	February
3	March
4	April
5	May
6	June
7	July
8	August
9	September
10	October
11	November
12	December

Than you connect the month name in this table to the month name in your Original table, you use the new table field in the visualizations and order by the first column.

Let me know if it worked.

Best Regards,

DR

[dax · Accepted solution]

Hi @manavmehta , 

You could create a column like below , then use this to "sort" Month column  which should work

column=year([Modified Date])*100+month([Modified Date])

Best Regards,
Zoe Zhi

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

[Anonymous · Accepted solution]

Hi @manavmehta ,

Yeah that order alphabetically and since there's more than one value to the same month PBI can't order it.

With this said you can create a table with "Enter Data" that would be like:

1	January
2	February
3	March
4	April
5	May
6	June
7	July
8	August
9	September
10	October
11	November
12	December

Than you connect the month name in this table to the month name in your Original table, you use the new table field in the visualizations and order by the first column.

Let me know if it worked.

Best Regards,

DR

[manavmehta]

Here is the snippet of the dataset.