Learning DAX: Calculating the First Day of the Week in Power BI

Mastering Date Intelligence and Week Calculation in Power BI

Analyzing time series data effectively within Power BI requires robust Date Intelligence capabilities. A frequent requirement in business reporting is determining the precise start date of a given week, which allows for accurate aggregation and comparison across weekly periods. Since different geographies or business standards define the start of the week differently (e.g., Sunday vs. Monday), the calculation must be flexible. We leverage DAX (Data Analysis Expressions) formulas to handle this task with precision. The following solutions provide standardized methods for deriving the first day of the week based on your specific organizational requirements.

You can use the following formulas in DAX to obtain the first day of the week for any specified date in your dataset:

Deconstructing the DAX Solution: Identifying Week Start

The foundation of these calculations lies in the WEEKDAY function. This function returns an integer representing the day of the week for a specified date. Crucially, WEEKDAY accepts an optional second argument, known as the return_type, which dictates which day is considered the start of the week (1=Sunday, 2=Monday, etc.). By subtracting the numerical representation of the current day from the date itself, we effectively “roll back” to the beginning of that week.

The following formulas, which are implemented as calculated columns in Power BI, demonstrate how to achieve the desired week start date based on two common starting conventions.

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 of these powerful formulas generate a new column named Week Start within your data model. This new column contains the specific date corresponding to the first day of the week for every corresponding entry in the primary Date column. Understanding the subtle difference in the return_type argument is essential for accurate reporting. We will explore the precise meaning of the return_type 2 utilized here in the subsequent sections, as it is key to ensuring flexibility.

Scenario 1: Calculating Week Start with Sunday as Day One

The most common week start convention in regions like the United States is Sunday. To correctly calculate the start of the week when Sunday is defined as Day 1 (or Day 7, depending on the system), we must utilize the WEEKDAY function effectively. In DAX, the standard return type definition often dictates that 1 represents Sunday, 2 represents Monday, and so on, up to 7 for Saturday.

However, notice that in Formula 1, we use the return_type 2. The return_type 2 configuration tells DAX to treat Monday as 1 and Sunday as 7. If we use the formula:

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

When WEEKDAY returns 7 (for Sunday), subtracting 7 days from the current date will land you on the previous Sunday. If the current date is Monday (WEEKDAY returns 1), subtracting 1 day lands you on the previous Sunday. This elegant mathematical manipulation ensures that regardless of the current day of the week, the formula successfully backs up the date to the most recent Sunday, establishing it as the correct Week Start date for the reporting period.

Scenario 2: Adapting the Formula for a Monday Week Start

In many international and business environments, the week formally begins on Monday. Adapting our DAX calculation for a Monday start is straightforward and involves a minor adjustment to the previous formula. We retain the use of return_type 2 in the WEEKDAY function because it inherently defines Monday as having a numerical value of 1.

If we use the base calculation:

'my_data'[Date] - WEEKDAY('my_data'[Date], 2)

this result consistently yields the previous Sunday, as explained in Scenario 1. To shift this result forward by one day, thereby landing on Monday, we simply add the number one (1) to the entire expression.

The finalized formula to designate Monday as the first day of the week is:

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

This small addition of + 1 shifts the calculated date from Sunday to Monday, providing precise alignment with ISO 8601 standards or internal business practices that favor Monday as the start of the working week. This flexibility demonstrates the power and simplicity of date manipulation within the DAX language when correctly applied.

Practical Implementation: A Step-by-Step Walkthrough in Power BI

To illustrate how these formulas are deployed, let us walk through a practical example using a sample dataset. Suppose we are managing sales records and need to aggregate performance data based on weekly cycles. We start with a dataset in Power BI containing information detailing total sales recorded on various dates.

Our initial dataset, named my_data, looks like this, showing transaction dates and corresponding sales figures:

Our objective is to augment this data table by creating a new column that explicitly lists the first day of the week for each corresponding date entry in the Date column. This new column will be instrumental for building weekly reports and visualizations.

To initiate the creation of this calculated column, navigate to the Table tools ribbon in Power BI Desktop and select the New column icon. This action opens the formula bar where you will input your DAX expression.

For our first demonstration, we will assume the week starts on Sunday. Type the following formula into the formula bar:

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

Upon pressing Enter, Power BI processes the calculation across all rows, resulting in a new column named Week Start. This column successfully contains the first day of the week (assuming Sunday is the start) for every transactional date:

Validating and Contextualizing the Sunday-Start Results

Reviewing the output of the Sunday-start calculation confirms the mathematical logic. When the formula is applied, any date within the calendar week (Sunday through Saturday) maps back precisely to the preceding Sunday.

Consider the following specific examples extracted from the table above, demonstrating the consistent mapping back to the Sunday starting date (1/7/2024):

• The first day of the week for the date 1/8/2024 (Monday) is correctly identified as 1/7/2024.
• The first day of the week for the date 1/10/2024 (Wednesday) is correctly identified as 1/7/2024.
• The first day of the week for the date 1/13/2024 (Saturday) is correctly identified as 1/7/2024.

As the calendar rolls into the next week, starting on 1/14/2024, the calculation updates:

• The first day of the week for the date 1/15/2024 (Monday) is correctly identified as the subsequent Sunday, 1/14/2024.

This method provides a reliable and auditable way to group dates into consistent weekly buckets essential for time-based analysis and reporting within Power BI.

Implementing the Monday-Start Convention

If your organization requires the week to be considered starting on Monday, you can easily implement the adjusted formula. This adaptation is crucial for businesses adhering to ISO standards or operating in regions where Monday is the standard start date. Simply modify the formula in the formula bar, adding the required offset of + 1:

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

Executing this modified expression will create the Week Start column where Monday is defined as the beginning of the reporting week. Observe how the dates shift accordingly compared to the previous example:

In this Monday-start scenario, dates like 1/8/2024, 1/10/2024, and 1/13/2024 all map back to 1/8/2024 (Monday), which is the true start of that reporting week. It is important to remember that using the correct return_type parameter for the WEEKDAY function is vital for the consistency of date calculations in DAX.

Note: You can find the complete and authoritative documentation detailing all potential return_type values and behavior for the WEEKDAY function in DAX on the official Microsoft documentation site. Understanding these parameters allows for customization beyond Sunday and Monday starts, accommodating diverse international standards.

Additional Resources for DAX and Power BI

The ability to manipulate dates and define custom time hierarchies is fundamental to advanced data analysis in Power BI. The following tutorials explain how to perform other common tasks and deeper analytical functions within the platform: