Here’s a question from our reader Melissa:
Quick question here: i am using the MONTH DAX function in Power BI to calculate the month number from a date. I can also convert a date to a month name with the DAX FORMAT function; or when loading the data in Power Quey with M. Now my question is provided that i have a month number column in DAX, how do i get the Month name in 3 letter (MMM) format – using DAX. I know that i can switch a pretty complex SWITCH statement – but that’s not what i am looking for. Any pointers?
Get month name from number with DAX -step by step
- Open your Power BI report (pbix file)
- Go to the Data View and highlight the table that contains your month number values that you would like to convert.
- Hit the New Column button.
- Create a new calculated column using the following DAX statement:
Month Name = FORMAT ( DATE (,2023, Dim_Dates [Month_Number],1), "MMMM")Note: Make sure to replace your table and Month number column names to the ones you are using in your data model.
- Explanation: the FORMAT function expects a date value to function correctly. As we have just the month number value, we need to create a synthetic date value with arbitrary year and day values.
- Optionally, add an additional column to represent names in MMM format (Jan, Feb etc’)
Month Name (MMM) = FORMAT ( DATE (2023, Dim_Dates[Month Number],1), "MMM")Our formula will render the following result:
You can now use the extracted month names in your visual reports – charts and tables.