Date.StartOfWeek - Date Function

Date.StartOfWeek is a Power Query M function that returns the start of the week containing the specified date(Time). Your input must be a date, datetime, or datetimezone value.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Date.StartOfWeek(
   dateTime as any,
   optional firstDayOfWeek as nullable number,
) as any

Argument	Attribute	Description
dateTime		A date, datetime or datetimezone value.
firstDayOfWeek	optional	The Day.Type specifies the starting day of the week. If not explicitly specified, the function defaults to Day.Sunday, aligning with regional week-start norms. It accepts values representing each day: – Day.Sunday (0) for Sunday, – Day.Monday (1) for Monday, – Day.Tuesday (2) for Tuesday, – Day.Wednesday (3) for Wednesday, – Day.Thursday (4) for Thursday, – Day.Friday (5) for Friday, – Day.Saturday (6) for Saturday.

Description

Date.StartOfWeek returns the start date of the week that contains the given date, datetime, or datetimezone value. You can provide an optional firstDayOfWeek, to indicate which day is considered the first day of the week. By default the function uses Day.Sunday.

Examples

The Date.StartOfWeek function works with date, datetime, and datetimezone values.

Shifting a Date Value

When you apply the Date.StartOfWeek function to a date value, it shifts the date to the start of the week. By default, the start of the week is considered to be Monday. Here’s an example:

// Output is a Monday: #date( 2024, 6, 17 )
Date.StartOfWeek( #date( 2024, 6, 18 ) )

This expression returns the first day of the week, which is June 17, 2024 (Monday).

To visualize this, imagine a table with a date column containing multiple date values. You can shift each of these to the start of their respective weeks by using Date.StartOfWeek.

Date.StartOfWeek shifts date value to beginning of week in Power Query M

Using the Day.Type Parameter

In case you want the start of the week to fall on a different day, you can make use of the Day.Type enumeration. Simply specify your desired start of the week day in the second argument of Date.StartOfWeek.

For instance, with Friday considered as the first day of the week, the Date.StartOfWeek function will return a Friday:

// Output is a Friday: #date( 2024, 7, 5 )
Date.StartOfWeek( #date( 2024, 7, 7 ), Day.Friday)

Here’s what that looks like for a range of dates:

Date.StartOfWeek supports a day.type to change start of week date in Power Query M

In this image, the values highlighted in green are the start-of-week values, and you can find the start of the week value specified in the second argument of Date.StartOfWeek.

Shifting a DateTime Value

The function also supports datetime values. To shift a datetime value of June 18th, 2024, 9:00 AM to the start of the week, you can use:

// Output: #datetime( 2024, 6, 17, 0, 0, 0 )
Date.StartOfWeek( #datetime( 2024, 6, 18, 9, 0, 0 ) )

Not only does this shift the date to the start of the week, but it also moves the time values to 12:00 AM on the first day of the week (Monday). Here are some example datetime values to illustrate this:

Date.StartOfWeek shifts date and time to beginning of week in Power Query M

Shifting a DateTimeZone Value

Similarly, applying the logic to a datetimezone value shifts the date to the start of the week and the time to the start of the day, while keeping the timezone information intact:

// Output: #datetimezone( 2024, 6, 17, 0, 0, 0, 1, 0 )
Date.StartOfWeek( #datetimezone( 2024, 6, 18, 19, 0, 0, 1, 0 ) )

Learn more about Date.StartOfWeek in the following articles:

Create Date Table or Calendar in Power Query M
Learn how to create a dynamic calendar table in Power Query’s M language. Build your custom columns and claim your free Date Table Script. » Read more

Other functions related to Date.StartOfWeek are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/date-startofweek