WeekStartDate & WeekEndDate in Power BI

 This article will teach us how to add the WeekDtart and WeekEnd dates based on a given date field.

To do this, let's ask ChatGPT to provide 100 random dates and load them into Power BI using the "Enter Data" option. Follow along with the steps below.

Step 1: Enter the Data and Rename the Field to Date

1. Open Power BI.
2. Use the "Enter Data" option to input the 100 random dates provided.
3. Once the data is entered, rename the field to "Date".

Step 2: Add the Start of Week Column

1. Navigate to the Add Column tab.
2. In this tab, look for the Date dropdown menu.
3. Select the Start of Week option to add a column that shows the start date of each week for the dates in your table.

After completing this step, you'll be able to view the start date of each week for the dates listed under the 'Date' field. If you observe closely, Power BI defaults to considering Sunday as the start of the week. To better understand this, let's add the 'Day Name' field to both the 'Date' and 'Start of Week' fields.

    To add the 'Day Name' field, select the date column to which we want to add the day name, then choose the 'Day Name' option under the 'Date' menu.

It's evident that Power BI defaults to considering Sunday as the start of the week. However, Power BI also offers ways to override this default behavior.

The first method is to edit the M-code directly in the formula bar, while the second method involves editing the applied step that created the 'Start of Week' column. Both methods entail modifying the M code used to create the 'Start of Week' column.

Let's proceed by updating the M code to consider Monday as the start of the week.

Let's break down the M-Code:

The Date.StartOfWeek() function includes an optional parameter where we can specify the numeric denotation for the start day of the week. For example:

• 0 --> Sunday
• 1 --> Monday
• 2 --> Tuesday
• 3 --> Wednesday
• 4 --> Thursday
• 5 --> Friday
• 6 --> Saturday

By providing '1' as the optional parameter, the start of the week column changes the start date to Monday.

However, using numeric values in M-Code might be confusing for others validating the developments or report codes. Fortunately, the M-Code Date.StartOfWeek() function also supports specifying the start of the week directly by its day name, as illustrated below.

We can follow similar steps to add an 'End of Week' column to the table.

By utilizing the Date.EndOfWeek() function, we can specify the end day of the week using numeric denotations or directly by its day name. This addition enables us to easily identify the end date of each week within the dataset.

Certainly, we've covered the concept of adding WeekStart and WeekEnd dates using Power BI's Power Query Editor. If you have any further questions or need more assistance, feel free to ask in the comments section!