Table of Contents
Power BI is a powerful data visualization tool that allows users to extract useful insights from their data. To get the day of the week from a given date, the user can utilize the “Calendar” function in Power BI. This function allows users to create a calendar table with dates and corresponding day of the week values. By linking this calendar table to the main data, the user can easily retrieve the day of the week for any given date and use it for analysis and visualization purposes.
You can use the following formulas in DAX to get the day of the week from a date in Power BI:
Formula 1: Get Day of Week as Full Name (“Sunday”)
day_of_week = FORMAT('my_data'[Date], "dddd")
Formula 2: Get Day of Week as Abbreviated Name (“Sun”)
day_of_week = FORMAT('my_data'[Date], "ddd")
Formula 3: Get Day of Week as Number (Sunday = 1)
day_of_week = WEEKDAY('my_data'[Date])
The following examples show how to use each method in practice with the following table in Power BI:
Example 1: Get Day of Week as Full Name
To get the day of the week as a full name, click the Table tools tab, then click the icon called New column:
Then type the following formula into the formula bar:
day_of_week = FORMAT('my_data'[Date], "dddd")
This will create a new column named day_of_week that displays the day of the week as a full name for the corresponding date in the Date column:
Example 2: Get Day of Week as Abbreviated Name
Then type the following formula into the formula bar:
day_of_week = FORMAT('my_data'[Date], "ddd")
This will create a new column named day_of_week that displays the day of the week as an abbreviated name for the corresponding date in the Date column:
Example 3: Get Day of Week as Number
To get the day of the week as a number from 1 to 7, click the Table tools tab, then click the icon called New column:
Then type the following formula into the formula bar:
day_of_week = WEEKDAY('my_data'[Date])
This will create a new column named day_of_week that displays the day of the week as a number for the corresponding date in the Date column:
Note: You can find the complete documentation for the FORMAT function in DAX .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: