Sorting months in text in a stacked column chart

[Matthias93]

Hi,

I have been trying to create a stacked column chart with months on the X-axis, unfortunately in the data I have the months are a string and not a date format. I looked up some solutions, but they all start from a date format. I would like for the months to show up in a chronological order and not an alphabetical one. Can anyone help me with this?

Many thanks in advance,

Matt

[gooranga1]

You could make a new column with the formula as;

Date = "01 " & Sheet1[Month] & " 2016"

Then model that as a date format. Then it you can add the calculated column. I imported a basic spreadsheet then created the new column.

I imported a spreadsheet

[DSimma]

I would usually create a new column which is just MonthNumber = Month(Date) which returned the numerical month number, I then select the text month column and set the sort by column to be the MonthNumber column. 

[Daniil]

Just create a calculated column with the following code and use it to sort the Reporting Period column:

MonthNo =
SWITCH (
    'Your Table Name'[Reporting Period],
    "January", 1,
    "February", 2,
    "March", 3,
    "April", 4,
    "May", 5,
    "June", 6,
    "July", 7,
    "August", 8,
    "September", 9,
    "October", 10,
    "November", 11,
    "December", 12
)

[Matthias93]

I tried it, but get the following error message:

The syntax for '"February"' is incorrect. (DAX(SWITCH ('Check-ins'[Reporting Period], "January", 1. "February", 2. "March", 3."April", 4. "May", 5."June", 6."July", 7."August", 8."September", 9. "October", 10 "November", 11 "December", 12))).

MonthNo =
SWITCH (
'Check-ins'[Reporting Period],
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)

Above you find what I filled in

[Daniil]

Matt, did you copy and paste my formula (having corrected the table name of course), or did you type it manually?

Note how the error message has periods (dots) after the first nine numbers, and nothing (not even commas) after the other numbers -- this is different from my formula.

[Matthias93]

Hi 

I was able to create the column, but when I try to sort my 'Reporting Period' column by the MonthNo it gives the follwoing error...

[Daniil]

Of course Power BI won't let you sort by calculated column derived from the column you want to sort 🙂 I forgot -- sorry about that.

Since you don't have a date column, I would simply add month number in Query Editor.

This code will give you the month names and corresponding month numbers:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUTJUitWJVnJLTSqCChiBBXwTi5IzgDxjMM+xoCgzB8gzgcqB1JmC2V6lealAjhmUkwOSMYfoKU0vLS4Bci3A3ODUgpLU3KTUIqCIJVjEP7kkH8I3NAAL+OWXwVQYQpzlkpoMFwG6KxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MonthName = _t, MonthNo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MonthName", type text}, {"MonthNo", Int64.Type}})
in
    #"Changed Type"

Merge this query with the Check-ins table and you should be able to sort Reporting Period column by MonthNo.

[sdjensen]

Depending on regional settings you might need to replace , with ; in the DAX code.