Table of Contents
Getting the first day of week in Power BI refers to identifying the first day of a week within a given date range. This can be useful for creating weekly reports or analyzing data based on a specific week. Some examples of getting the first day of week in Power BI include using the DATEADD function to find the first day of the current week, using the DATEDIFF function to calculate the number of days between a given date and the first day of the week, or using the WEEKDAY function to determine the numerical value for the first day of the week (e.g. 1 for Sunday, 2 for Monday).
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:
- 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:
Note: You can find the complete documentation for the WEEKDAY function in DAX .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: