Here’s a question from a reader:
I have a table containing date time information in a mm/dd/yyyy format. When importing the dataset into Power Bi Desktop, a Date Hierarchy is automatically created. It contains the Year, Quarter, Month and Day values. I am specifically looking to add a day of the week value to my model. Any ideas?
Extract day of the week from date time – Example
Step 1: Import dataset to Power Bi Desktop
- If you haven’t imported your data yet to Power BI, use the Get Data capability to import your data set from Excel, text, csv, SQL, or a a OneLake data hub
- In this example i have imported a very simple dataset based on an Excel spreadsheet.
- Here’s a screenshot of the model:
Step 2: Convert date to week day in Power BI with DAX
- In Power BI, select the table containing your Dates.
- In the Table Tools menu hit the New Column, to define a new calculated column.
- Then type the following DAX expression in your formula bar:
Day of Week = FORMAT(Dates_tutorial[Date], "dddd")
- This will create a column in your table containing the week days corresponding to the each date in the table: Sunday, Monday etc’.
- We can also derive the abbreviated short day of the week name: Sun, Mon etc’ using the following expression:
Day of Week (Short) = FORMAT(Dates_tutorial[Date], "ddd")
- Here’s our table with the newly added calculated columns:
Step 3: Group data by day of the week
This step is optional and show for completeness.
Proceed as following to summarize your data according to day of the week.
- In Power BI Desktop, move to the Table View.
- Hit the New Table command.
- Then use the following DAX table to create the following summary table:
Interviews by Week = SUMMARIZE(Dates_tutorial,Dates_tutorial[Day of Week],"Number of Interviews", SUM(Dates_tutorial[Interviews]))
- Commit your changes – a new summary table was created in your Data Model showing the number of interviews per week.
Note: you might want to sort out your summary table according chronologically. We touched on that in our tutorial on sorting Power BI tables according to specific column values.