Downloads
Power Query MDateIntermediate

Date.WeekOfYear

Returns the week number of a date within its year.

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

Returns: The week number as a number, or null when the input is null.

What it does

Date.WeekOfYear returns the number of the week in the year. The result depends on which day you count as the first day of the week, through the firstDayOfWeek argument. Week numbers are a common source of confusion, because different systems number the first week of a year differently. A team should confirm the rule against its own calendar before it uses the numbers in a report.

Syntax and arguments

Example

A weekly sales summary groups days into weeks that start on Monday. Each day is labeled with the Monday that starts its week, which avoids depending on week numbers that might not match the company's calendar.

let
    Sales = #table(type table [SaleDate = date, Amount = number],
        {{#date(2026, 10, 26), 300}, {#date(2026, 10, 29), 450}, {#date(2026, 10, 31), 200}}),
    WeekStart = Table.AddColumn(Sales, "WeekStarting", each Date.StartOfWeek([SaleDate], Day.Monday), type date),
    Weekly = Table.Group(WeekStart, {"WeekStarting"}, {{"Sales", each List.Sum([Amount]), type number}})
in
    Weekly

October 26, 29 and 31 all fall in the week that starts on Monday, October 26, so the three days group into one row with sales of 950. Labeling by the week's start date is clearer than a week number.

Example: a second, different use

Use Date.WeekOfYear to number weeks in a dashboard once the start of the week has been agreed with the company.

Things to watch

In business terms

Weekly sales, weekly cash and weekly labor reports are common in retail and services. Agreeing the week definition with the business, and labeling weeks by their start date, keeps those reports in line with the way the company counts its weeks.

Related functions

All Power Query M functions · View as Markdown · M basics