Calculate ISO 8601 week in DAX with hidden parameter

Happy New Year to all my readers!

The first challenge this year is the correct calculation of the calendar week. Because this year we have again the case that the calendar weeks differ between America and Europe.

Different calculation of the calendar week between North America and Europe

While in North America the first week of the new year is always defined as week 1, Europe follows the ISO 8601 standard. According to ISO calculation in Europe and Asia, the first week in the new year, which contains 4 or more days, is defined as week 1. In the USA and Canada, there may also be a half week as week 1.

In 2021, the first week of the new year is therefore divided in America:

In Europe, the first week of 4 full days is defined as the first calendar week of the year:

Calculation with the WEEKNUM function

Basically, the calendar week can be calculated using the WEEKNUM function in DAX. The syntax looks like this:

WEEKNUM(<date>, <return_type>)

The first parameter <date> is the date. According to the official documentation, the <return_type> is either a 1 if the new week starts on a Sundayor a 2 if the new week starts on Monday.

return_type 21 for ISO 8601 format

However, there is another value for the parameter which is not mentioned in the documentation. If we specify the value 21as <return_type>, the calendar week will be returned in ISO 8601 format, i.e. European, Asian and Oceanic format. The week starts on Monday anyway, according to ISO.

WEEKNUM([Date], 21)

Here are the 3 values in comparison:

Summary

So we can use the WEEKNUM function to map all 3 possibilities of the calendar week:

1. With 1 as <return_type> in North American format and week start Sunday.
2. With 2 as <return_type> in North American format and week start Monday.
3. With 21as <return_type> European ISO format and week start Monday.