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]
n > 0 && n < 13 ,
-- THEN --
-- ELSE --
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.