How to convert dates to day of the week in Power BI?

by

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.