DAX : Convert Number into Month Name

I sometimes see requests asking how to convert a number between 1 and 12 into a month name such as “Jan” or “February.

The most common solution I see offered is along the lines of a SWITCH statement that lists 12 conditions (one for each month).   This works, but can also be done using existing functions.

While DAX lacks a dedicated function to convert a number to a text version, such as DATENAME in T-SQL, we can get there in two functions using DATEVALUE wrapped in a FORMAT.

To demonstrate, I will create a simple table with 13 values (1 through 13) using the following calculated table.

Table = GENERATESERIES(1,13)

This creates a single column table with 13 rows.

Now I can use the DATEVALUE and FORMAT functions in a calculated column as follows:

Month Name = 
VAR n = 'Table'[Month Number]
RETURN 
    IF(
        n > 0 && n < 13 ,
        -- THEN --
        FORMAT(DATEVALUE("2018-" & n & "-1") , "MMM") ,
        -- ELSE --
        "Other"
        )

Which produces the following result:

As you can see, the FORMAT function is using “MMM” which produces the short name version of month (eg. Jan, Feb, Mar etc.).  If the full month name is required, simply replace “MMM” with “MMMM”

The trick is to generate a text in date format that is easy to convert.  In my case the number three will be built into a concatenated string such as “2018-3-1”.  The fact I use 2018 is only important to make DATEVALUE recognize the string as a value that can be converted to a string.  As we are only after the month anyway, this is eventually discarded.

Philip Seamark

Phil is Microsoft Data Platform MVP and an experienced database and business intelligence (BI) professional with a deep knowledge of the Microsoft B.I. stack along with extensive knowledge of data warehouse (DW) methodologies and enterprise data modelling. He has 25+ years experience in this field and an active member of Power BI community.