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.
Convert Month Name to Month Number in Power BI
A tangent question we got is how to convert month names (like “January” or “Jan”) to their corresponding numbers (1-12) in Power BI?
To convert month names to numbers in Power BI, we’ll use a calculated column with a SWITCH function. Follow the steps outlined below:
- Go to the Data View in Power BI Desktop.
- Select the table containing your month names.
- Click “New Column” in the Modeling tab.
- Enter the following DAX formula:
Month Number =
SWITCH(
UPPER(LEFT([Month Name], 3)),
"JAN", 1, "FEB", 2, "MAR", 3, "APR", 4, "MAY", 5, "JUN", 6,
"JUL", 7, "AUG", 8, "SEP", 9, "OCT", 10, "NOV", 11, "DEC", 12
)This formula works by taking the first three letters of the month name (converted to uppercase) and matching it to the corresponding number. You can modify accordingly if your month column format is somewhat different.
To use the month numbers column in a Power BI visual:
- Create a table or matrix visual.
- Drag your original “Month Name” field to the Rows.
- Drag the new “Month Number” field to the Values.
- Sort the visual by the “Month Number” column for chronological order.
Troubleshooting: If you get errors, ensure your month names are spelled correctly and consistently. For blank results, check if your month names are in a different language or format.