Sort the visualization by month

[tuncay]

I have a big list of data, where I have specific data sort by month about an organizational unit. When I create a visual, I try to sort the column month by monthNum, but it says: 

The Month column cannot be sorted by MonthNum. There cannot be more than one value for the same value in Month.

Since I have a list, where the months are listed in one column (type: text), it doesn't work. Is there any solution for this?

I would try changing the type of Month to date, but I have also a rows in that "Month" column like YTD, or ACT YTD PY. Need ur help, thanks.

[FarhanAhmed · Accepted solution]

This is because your MonthNum has different values for Same Month 

e.g. Jan has 169,183,197 etc causing issues while sorting

to make this work your each text value must have the same Numeric value for sorting.

Try create a calculated column to give you values 

- Jan =1

- Feb =2 

 and so on.

Then apply sort by option and it will work for you.

Note: This calculated column should be done in Power Query or in Data Source.

[tuncay]

I tried this:

Spoiler

if [Month] = "ACT YTD PY" then 1
else if [Month] = "Jan" then 2
else if [Month] = "Feb" then 3
else if [Month] = "Mar" then 4
else if [Month] = "Apr" then 5
else if [Month] = "May" then 6
else if [Month] = "Jun" then 7
else if [Month] = "Jul" then 8
else if [Month] = "Aug" then 9
else if [Month] = "Sep" then 10
else if [Month] = "Oct" then 11
else if [Month] = "Nov" then 12
else if [Month] = "Dec" then 13
else if [Month] = "YTD" then 14

else 15

But it only shows me 1, 15 and 14. It doesn't work so well. Why?

[FarhanAhmed]

This should work.

Make sure that you don't have any trailing or leading spaces in your data.

Try Trim before applying this step.

[tuncay]

Trimmed month column. Still showing the numbers: 15, 14, 1 only 😞

[FarhanAhmed]

is it possible for you to share the PBIX file after removing sensitive data

[tuncay]

Unfortunately, this is not possible. Maybe I could try doing something else. Any ideas?

edit: I had to add a blank space after the values 😄 (e. g.: "Jan ") works now, thank you

[FarhanAhmed]

See this file helps.

Also make sure that Trim, Clean is being applied also check the case sensitivity of the data itself while comparing.

if possible kindly share the screenshot of the calculated column like below