How to Get First Day of Week in Power BI (With Examples)


You can use the following formulas in DAX to get the first day of the week for a given date:

Formula 1: Get First Day of Week (Assuming First Day is Sunday)

Week Start = 'my_data'[Date] - WEEKDAY('my_data'[Date], 2)

Formula 2: Get First Day of Week (Assuming First Day is Monday)

Week Start = 'my_data'[Date] - WEEKDAY('my_data'[Date], 2) + 1

Both formulas create a new column named Week Start that contains the first day of the week for the corresponding date in the Date column.

The following example shows how to use both formulas in practice.

Example: How to Get First Day of Week in Power BI

Suppose we have the following dataset in Power BI that contains information about total sales made on various dates by some company:

Suppose that we would like to create a new column that contains the first day of the week for each corresponding date in the Date column of the table.

To do so, click the Table tools tab and then click the New column icon:

Then type the following formula into the formula bar:

Week Start = 'my_data'[Date] - WEEKDAY('my_data'[Date], 2)

This will create a new column named Week Start that contains the first day of the week (assuming the first day is considered Sunday) for each corresponding date in the Date column:

Power BI get first day of week

For example, we can see:

  • The first day of the week for the date 1/8/2024 is 1/7/2024.
  • The first day of the week for the date 1/10/2024 is 1/7/2024.
  • The first day of the week for the date 1/13/2024 is 1/7/2024.
  • The first day of the week for the date 1/15/2024 is 1/14/2024.

And so on.

If you would like the first day of the week to be considered Monday instead, then you can type the following formula into the formula bar:

Week Start = 'my_data'[Date] - WEEKDAY('my_data'[Date], 2) + 1

This will create a new column named Week Start that contains the first day of the week (assuming the first day is considered Sunday) for each corresponding date in the Date column:

Power BI get first day of week using Monday as start of week

Note: You can find the complete documentation for the WEEKDAY function in DAX here.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

Power BI: How to Filter Between Two Dates
Power BI: How to Extract Month from Date
Power BI: How to Extract Year from Date

One Reply to “How to Get First Day of Week in Power BI (With Examples)”

  1. Thanks for this post! It was helpful in my work. One call out though: I’m not sure the formula for “Get First Day of Week (Assuming First Day is Sunday)” is working as expected. In your example, the “First Day of the Week” is showing as “1/28/2024” for the date “2/4/2024”, but I would expect the “First Day of the Week” to show as “2/4/2024”, since “2/4/2024” is a Sunday. The following formula may make more sense to use for finding the first Sunday of each week: [Date] – WEEKDAY([Date],1)+1

Leave a Reply

Your email address will not be published. Required fields are marked *