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?