This post is also available in: Deutsch
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:
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.
Here are the 3 values in comparison:
So we can use the WEEKNUM function to map all 3 possibilities of the calendar week:
- With 1 as <return_type> in North American format and week start Sunday.
- With 2 as <return_type> in North American format and week start Monday.
- With 21as <return_type> European ISO format and week start Monday.