How can I use Power BI to retrieve the day of the week from a given date?

By /

Power BI is a powerful data analysis tool that allows users to retrieve and manipulate data from various sources. One of its useful features is the ability to retrieve the day of the week from a given date. This can be achieved by using the DAX function called WEEKDAY, which calculates the day of the week from a given date and returns a number between 1 and 7, with 1 representing Sunday and 7 representing Saturday. This feature can be particularly helpful for businesses and organizations that need to track and analyze data based on specific days of the week. By utilizing the WEEKDAY function in Power BI, users can easily retrieve and visualize this information, providing valuable insights and aiding in decision-making processes.

Power BI: Get Day of Week from Date

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:

Power BI get day of week from date

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:

Power BI get day of week as abbreviated name

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:

Power BI get day of week as number

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:

x

Related terms:

  1. How can I use Power BI to get the day of the week from a given date?
  2. Excel: Get the Date of a Given Week Number
  3. How do I add a week to the date hierarchy in Power BI?
  4. How can I add a week to a date hierarchy in Power BI?
  5. How can I use Google Sheets to calculate age from a given date of birth?
  6. How can I retrieve distinct values from multiple columns using Power BI?
  7. How can I use Power BI to calculate the number of days in a given month?
  8. How can I calculate 90 days from a given date in Excel?
  9. Is the Date Between Two Given Dates in R?
  10. What is the process for obtaining the first day of the week in Power BI and can you provide some examples?