Convert Month Name to Month No

[Khomotjo]

Hello Everyone, I have a coloumn that has month names and want to convert these to integers so that I can use Date function( I have coloumns that have the day and the year ).

I tried :

switch (month_name,

"January",1,
"February",2,

"March",3 etc

But get error that I cannot compare text with a number. 

[Bibiano_Geraldo · Accepted solution]

Hi @Khomotjo ,

Please use the bellow DAX to achieve your goal:

MonthNumber = 
SWITCH(
    TRUE(),
    'Table'[month_name] = "January", 1,
    'Table'[month_name] = "February", 2,
    'Table'[month_name] = "March", 3,
    'Table'[month_name] = "April", 4,
    'Table'[month_name] = "May", 5,
    'Table'[month_name] = "June", 6,
    'Table'[month_name] = "July", 7,
    'Table'[month_name] = "August", 8,
    'Table'[month_name] = "September", 9,
    'Table'[month_name] = "October", 10,
    'Table'[month_name] = "November", 11,
    'Table'[month_name] = "December", 12,
    BLANK() -- Default case if no match is found
)

Make sure to replace 'Table'[month_name] with name of your table and column that contain months as String

[danextian]

Hi @Khomotjo 

You can concatenate the month name with a day and year to get date representation as text, convert it to actual date using DATEVALUE and wrap it with MONTH to get the month number.

Month Number =
-- This measure converts a month name into its corresponding month number.
VAR DateAsText = -- Concatenate the 'Month Name' column with a placeholder day ("1") and year ("2024") 
-- to create a full date string (e.g., "January 1, 2024").
'Table'[Month Name] & " 1, " & "2024"
RETURN
    -- Convert the date string into a date value and extract the month number.
    MONTH (
        DATEVALUE ( DateAsText )
    )

[Uzi2019]

Hi   @Khomotjo 

Try below dax and create calculated column

MonthNo = SWITCH('Year - Month'[Month], 
        "Jan", 1,
        "Feb",2,
        "Mar",3,
        "Apr",4,
        "May",5,
        "Jun",6,
        "Jul",7, 
        "Aug",8,
        "Sep",9,
        "Oct",10,
        "Nov",11,
        "Dec",12
                )

Or  try this

 or you can try this to create date column

Month Name = 
SWITCH(Table[Month],
   "January", Format (Date(2020,1,1), “mmm”),
   "February", Format (Date(2020,2,1), “mmm”),
//and so on for the rest of the months
)

I hope above method would work for you.

I hope I answered your question!

@Khomotjo

[DataNinja777]

Hi @Khomotjo ,

To address the issue of converting month names into integers in DAX, the error you encountered arises because the SWITCH function requires consistent data types for its return values. One way to resolve this is by using a properly structured SWITCH function, ensuring that the values returned are explicitly integers. Here is an example:

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

Alternatively, if your data model allows, you can create a mapping table with two columns: Month Name and Month Number. You would then establish a relationship between your main table and this mapping table using the Month Name column as the key. A calculated column in the main table can then reference the Month Number using the RELATED function, like so:

MonthNumber = RELATED('Month Mapping'[Month Number])

For another approach, you can use Power Query to handle this transformation by adding a conditional column that maps each month name to its corresponding integer value. If your data contains consistent English month names, you can also dynamically parse the month number using the DATEVALUE function by concatenating the month name, a day, and the year into a valid date format. For example:

MonthNumber = MONTH(DATEVALUE('Table'[Month Name] & " 1, " & 'Table'[Year]))

This method leverages the built-in ability of the DATEVALUE function to interpret text-based month names, making it efficient and dynamic for cases with a valid Year column. Each of these approaches addresses the problem while ensuring flexibility and ease of use in Power BI.

Best regards,