How does the WEEKNUM function (DAX) work?

The WEEKNUM function (DAX) returns the week number for the given date and year according to the return_type value. The week number indicates where the week falls numerically within a year.

WEEKNUM Formula Syntax

WEEKNUM (
<date>, <return_type>
)

How do you use the WEEKNUM function?

The WEEKNUM function starts counting with the week that contains January 1. The return_type argument controls which day of the week is used to begin a new week number. Return_type is optional and defaults to 1.

With a return_type of 1-17, week number 1 in a given year is assigned to the week that contains January 1. With return_type 21, week 1 is the week containing the first Thursday of the year, following ISO 8601.

Considerations when using the WEEKNUM function

1. If we omit the return_type argument, it will take the default value of 1.
2. MS Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2009 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel interprets text representations of dates differently, depending on the date settings on your computer. Therefore, the serial_number argument for the WEEKDAY function should be entered as either:
1. A reference to a cell containing a date
2. A date returned from another function or formula

Formula examples using the WEEKNUM function

=WEEKNUM(“Feb 14, 2010”, 2)

=WEEKNUM(‘Employees'[HireDate])

=WEEKNUM(“12/24/2016”)