How to add a month number column in Power bi?

Melvin asks:

I have a date column in my Power BI model. Can you explain how to create both month name and month number to the Power BI report i need to prepare for the next management meeting?

Creating month columns in Power BI

Define a date column

In case you still don’t have a date column and want to follow along with this tutorial, you need to create a table containing a column of date / time data type.

  • Open your Power Bi report.
  • Open the Data View.
  • Vreate such a column in DAX using the CALENDAR function:
Dates = CALENDAR (DATE (2024,1,20), DATE(2024,2,5))
  • This will create a Dates table:

Note: At this point you might optionally define your table as a Date table.

Create a month number column

Adding the month name is easy with DAX:

  • Stay in the Data View.
  • Hit the New Column button.
  • Type the following DAX formula to create your Month Number column.
Month Number = MONTH(Dates[Date])

Note: you can easily add a day number column by using this expression:

Month Number = DAY (Dates[Date])

Adding the month name

For the month name we will use the DAX FORMAT function:

  • Again, press the New Column Ribbon button.
  • Type the following DAX:
Month Name = FORMAT (Dates[Date], "mmmm")

Create a Week Day column

Similarily you can create a Day column using the following expression

Day of Week Name = FORMAT (Dates[Date], "dddd")

Find if a date is a working day

We will use a simple logic to find whether a specific day is a working day. This might not be 100% correct as vacations are not taken under control.

Business Day = IF (OR (WEEKDAY(Dates[Date]) = 1, WEEKDAY(Dates[Date])=7 ) , "NO", "YES")

Sundays and Saturdays will be marked as non-working days:

Putting all together:

  • Save your report.

Follow up learning

How to calculate time differences between columns in Power BI?