Date.WeekOfYear

Date.WeekOfYear is a Power Query M function that returns a number from 1 to 54 indicating which week of the year the date, dateTime, falls in. An optional firstDayOfWeek parameter can be specified.

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

Syntax

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

Argument	Attribute	Description
dateTime		The date value to determine the week number of the year from.
firstDayOfWeek	optional	Determines the starting day for the week. If this argument is not specified, the function defaults to the first day of the week as defined by the cultural settings of your system. To set a custom starting day, you can use the Day.Type enumeration, which includes options like: Day.Monday, Day.Tuesday, Day.Wednesday, Day.Thursday, Day.Friday, Day.Saturday, Day.Sunday

Description

The Date.WeekOfYear function calculates the week number for a given date within a year, returning an integer between 1 and 54. This function requires a date as input and evaluates it against the year’s calendar to determine its corresponding week number.

It defaults to using your computer’s local settings for defining the week’s start day, but allows for customization through the Day.Type enumeration (e.g., Day.Monday, Day.Sunday). If no specific start day is provided, the function automatically adopts a start day based on cultural norms associated with the machine’s locale settings.

Examples

Determining the Week Number for a Specific Date

You want to find out the week number into which June 29, 2024, falls. To do that you can use:

Date.WeekOfYear( #date( 2024, 6, 29 ) ) // Output: 26

In this example, the Date.WeekOfYear function calculates the week number of the year for the given date, June 29, 2024. By default, the function adheres to your computer’s local settings to decide the starting day of the week. The result indicates that June 29, 2024, is in the 26th week of that year.

Specifying the Start Day of the Week

For another calculation, you want to return the week number for June 29, 2024, with Tuesday as the starting day of the week.

Date.WeekOfYear( #date( 2024, 6, 29 ), Day.Tuesday ) // Output: 27

This example highlights the function’s capability to accommodate different starting days of the week. By specifying a Day.Type as the second parameter, in this case Day.Tuesday, the function reevaluates the week number with Tuesday as the first day. As a result, the week number for June 29, 2024, changes to 27, reflecting the impact of the selected start day on the week’s calculation.

Related articles

Learn more about Date.WeekOfYear 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

Related functions

Other functions related to Date.WeekOfYear are:

• Date.Day
• Date.DayOfWeek
• Date.DayOfYear
• Date.DaysInMonth
• Date.Month
• Date.QuarterOfYear
• Date.WeekOfMonth
• Date.Year

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