OpenBI Docs Buy

WEEKNUM

The week of the year of a date: weeks starting on Sunday by default, on another day by type, or the ISO week (type 21).

Group: Date and time. Compatible with DAX.

Syntax

WEEKNUM ( <date> [, <return_type>] )
ParameterTypeDescription
dateDateThe date whose week is wanted.
return_typeWhole number
optional		Which day starts a week: 1 or 17 Sunday (1 when left out), 2 or 11 Monday, 12 Tuesday to 16 Saturday; 21 for the ISO 8601 week.

Returns

Whole number. The week's number in its year.

Remarks

  • Except with 21, week 1 is the week that holds 1 January, so the first and the last week of a year are often short, and a year can have 53 or even 54 weeks.
  • With 21, the ISO week: weeks run Monday to Sunday and week 1 is the one with the year's first Thursday. Early January days can be in week 52 or 53 of the year before, late December days in week 1 of the next.
  • The ISO week's year then differs from YEAR of the date; it is YEAR ( date - WEEKDAY ( date, 2 ) + 4 ), the year of that week's Thursday.
  • A blank date gives BLANK; a type not listed is an error.

Examples

Around New Year 2021

1 January 2021 was a Friday: week 1 by type 1, but week 53 of 2020 by ISO.

EVALUATE
VAR Days =
    DATATABLE ( "Day", DATETIME, { { "2020-12-31" }, { "2021-01-01" }, { "2021-01-03" }, { "2021-01-04" } } )
RETURN
    ADDCOLUMNS (
        Days,
        "WEEKNUM", WEEKNUM ( [Day] ),
        "ISO week", WEEKNUM ( [Day], 21 ),
        "ISO year", YEAR ( [Day] - WEEKDAY ( [Day], 2 ) + 4 )
    )
DayWEEKNUMISO weekISO year
2020-12-3153532020
2021-01-011532020
2021-01-032532020
2021-01-04212021

Weeks from Sunday and from Monday

1 January 2024 was a Monday; the 7th a Sunday.

EVALUATE
ROW (
    "7 Jan, from Sunday", WEEKNUM ( DATE ( 2024, 1, 7 ) ),
    "7 Jan, from Monday", WEEKNUM ( DATE ( 2024, 1, 7 ), 2 ),
    "31 Dec, from Sunday", WEEKNUM ( DATE ( 2024, 12, 31 ) )
)
7 Jan, from Sunday7 Jan, from Monday31 Dec, from Sunday
2153

WEEKDAY, YEAR, DATE, STARTOFWEEK.

Last updated · OpenBI Studio 0.3.74