Table of Contents
The process of obtaining the first day of the week in Power BI involves using a combination of functions and variables to calculate the first day of the week based on a given date. This can be achieved by creating a custom column or measure in the Power BI data model. The specific steps for obtaining the first day of the week may vary depending on the data source and desired outcome, but generally involve using the DAX function “WEEKDAY” and setting the start of the week to the desired day (e.g. Sunday or Monday).
For example, if the desired outcome is to have the first day of the week as Sunday, the DAX formula would be:
= [Date] – WEEKDAY([Date], 1) + 1
This would return the date of the first day of the week for each date in the “Date” column. Another example, if the desired outcome is to have the first day of the week as Monday, the DAX formula would be:
= [Date] – WEEKDAY([Date]) + 1
This would return the date of the first day of the week for each date in the “Date” column, with Monday as the first day.
Overall, the process for obtaining the first day of the week in Power BI involves using DAX functions and variables to calculate the desired outcome and can be customized to fit specific needs.
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:
- 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: