Date.EndOfWeek - Date Function

Date.EndOfWeek is a Power Query M function that returns the end of the week, with an optional parameter to set the first day of the week. The function returns the end of the week as a date, datetime, or datetimezone value.

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

Syntax

Date.EndOfWeek(
   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.EndOfWeek returns the end of the week for a given date, datetime, or datetimezone value (dateTime). An optional parameter (firstDayOfWeek) can be used to set the first day of the week. By default, Day.Sunday is used.

Examples

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

Shifting a Date Value

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

// Output is a Sunday: #date( 2024, 6, 23 )
Date.EndOfWeek( #date( 2024, 6, 18 ) )

This expression returns the last day of the week, which is June 23, 2024 (Sunday).

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

Date.EndOfWeek Shifts Dates to Sunday in Power Query

Using the Day.Type Parameter

In case you want the start of the week (and with that also the end 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.EndOfWeek.

For instance, with Wednesday considered as first day of the week the Date.EndOfWeek function will return a Tuesday:

// Output is a Tuesday: #date( 2024, 6, 18 )
Date.EndOfWeek( #date( 2024, 6, 18 ), Day.Wednesday )

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

Date.EndOfWeek Using FirstDayOfWeek with Day.Type in Power Query

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

Shifting a DateTime Value

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

// Output: #datetime( 2024, 6, 23, 23, 59, 59.9999999 )
Date.EndOfWeek( #datetime( 2024, 6, 18, 0, 0, 0 ) )

Not only does this shift the date to the end of the week, but it also moves the time values to 11:59 PM on the last day of the week (Sunday). Here are some example datetime values to illustrate this:

Date.EndOfWeek Shifts Date and Time value of Datetime values in Power Query M

Shifting a DateTimeZone Value

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

// Output: #datetimezone( 2024, 6, 23, 23, 59, 59.9999999, 1, 0 )
Date.EndOfWeek( #datetimezone( 2024, 6, 18, 0, 0, 0, 1, 0 ) )

Learn more about Date.EndOfWeek 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.EndOfWeek are:

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