Date.WeekOfMonth - Date Function

Date.WeekOfMonth is a Power Query M function that returns a number from 1 to 6 indicating which week of the month the date dateTime falls in.

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

Syntax

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

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

For a provided datetime value (dateTime), Date.WeekOfMonth returns a number from 1 to 6. This number indicates which week of the month dateTime falls into.

Examples

Determining the Week Number of a Date

To find out which week number of a month your date belongs to, you can use the Date.WeekOfMonth function. Here’s how to do it for the date January 11, 2024:

// Output: 2
Date.WeekOfMonth( #date( 2024, 1, 11 ) )

Since the value belongs to the second week of January, it returns an integer with the number 2. To get a better understanding, take a look at this image that applies the function to a range of dates:

Date.WeekOfMonth returns the week number of a date in Power Query

Using the Day.Type Parameter

The function also supports a Day.Type enumeration. This allows you to specify what you consider the start of the week. For instance, if we take our previous example of January 11, 2024:

// Output: 3
Date.WeekOfMonth( #date( 2024, 1, 11 ), Day.Thursday )

Now, instead of returning 2, the Day.Thursday enumeration changes the start of the week to Thursday. As a result, the value now belongs to week number 3.

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

Date.WeekOfMonth supports a day.type to change the start of the week in Power Query

As the image shows, the week number increments each time it reaches a Thursday. We specified this behavior by using Day.Thursday as the Day.Type enumeration.

Applying to a DateTime Value

You can also apply this function to a datetime value:

// Outcome: 4
Date.WeekOfMonth( #datetime( 2024, 6, 20, 10, 30, 0) )

The date June 20, 2024, belongs to week 4 of June.

Applying to a DateTimeZone Value

Likewise, you can also retrieve the week of the month value from a datetimezone value:

// Outcome: 4
Date.WeekOfMonth( #datetimezone( 2024, 6, 20, 10, 30, 0, 3, 0) )

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

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