Sorting by Month

[Anonymous]

Hi all,

I'm having a bit of a headache when trying to sort a graph by month. Normally it's no problem but this time my months are located in different years (some in 2017 and some in 2018). When I have the graph at year level I have no issues, but when I drill down in every year at the same time, the months are sorted wrong.

Does anyone know how to fix this? I have a regular database from excel and can change data type and create extra columns.

Thanks in advance!

[Anonymous]

You could create a column where you concatenate both year and month like January 2017, February 2017, and so on. Then put an index column. From there you can sort your Month by the index column, which is basically the sequence of the concatenated column of Month and Year. Or you could create a parameter table of months from January to December with index 1 to 12, then connect the months in your data to that table, which is sorted by the index. You could then use the months in the parameter table in your chart. 

[Anonymous]

Hi @Anonymous

This won't work for me, as I have dublicates in my date column and Power BI will not allow me to sort by index, as there are multiple index values for the same date.. 

But thanks for the input!

[Greg_Deckler]

Create a column for a month name using something like "FORMAT([Date],"mmm")" to get the month name. Create another one column that is MONTH([Date]). Now sort the month name field by the month number field. Use the month name field in your visual.

That being said, if you are working with a real Date or Date/Time field and using the built-in hierarchy, it should sort the months correctly.

[Greg_Deckler]

Sample data would help to recreate and it would also help to know how the months are being sorted, alphabetically? Generally if they are being sorted alphabetically you need to create a numeric month field and set your Sort By field to this numeric month column.

[Anonymous]

Hi Greg - my graphs look like this, where the second one is where I drill into both years at the same time (January, March, April is 2019, rest is 2018). So it seems that the months are being sorted as if all the data were in the same year.

A sample of the date data is below:

Hope you can help 🙂