If you are not in the US, chances are that your organization works with a calendar based on the ISO 8601 week numbering system. In the ISO standard, weeks start on a Monday and are always seven days long. When working with Power BI, ISO week numbers can be easily retrieved with the DAX WEEKNUM() function using the special return type 21 (which the DAX editor does not suggest, by the way):

ISO Week = WEEKNUM([Date], 21)

This is great if you want to create a calendar table in DAX. Things become more difficult when you want to generate a calendar table in Power Query: the built-in Date.WeekOfYear function does not return ISO week numbers at all. Many people have published scripts to determine the ISO week numbers that are often quite complicated. In this blog post, I show that with some logic, you only need two lines of code to do this. Super easy!

Let’s start with revisiting the definition of the ISO week number system. As I already mentioned, each week starts on a Monday. The core of the definition is either one of the two rules below to determine the first week of the year:

• The 4^th of January is always in week 1
• The first Thursday of the year is always in week 1

There are a couple of consequences to this definition. One of the most important ones is that the 1^st of January is not always in the same calendar year as the 4^th, meaning that we need to use a specific ISO year. An ISO year can have 52 or 53 weeks; sometimes, the first days of January are in the previous ISO year (relative to the calendar year). And sometimes, the last days of December are in the next ISO year.

Other useful observations are that Thursdays are the mid-day of the week and that on Thursdays, the ISO year is always equal to the calendar year. This gives us a straightforward way to determine the ISO year for any date: just move to the nearest Thursday and retrieve the year. Here is the M code to find the nearest Thursday:

ClosestThursday = Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3)

Here, DateInput is the date for which we want to find the ISO year. The function Date.DayOfWeek returns the day number in the week, starting with 0 on Mondays (hence the Day.Monday argument). So, we take the date, subtract the day number (this will bring us to the Monday preceding our date) and add three days to end at the Thursday.

The complete M code for a custom function to determine the ISO year is below:

(DateInput as date) as number =>

let

    ClosestThursday = Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3),

    Year = Date.Year(ClosestThursday)

in

    Year

Now, let us move on to actual ISO week numbers. The common approach is to somehow determine how far a date is from either the 4^th of January or the first Thursday of the year. The trouble comes near the end of the year, where this approach becomes tricky; after all, dates could fall in the next (ISO) year and the distance to the start of the year may not be relevant at all.

However, things become really easy when you consider that as Thursdays are the mid-day of the week, each date has the same ISO week number as the Thursday nearest to it. We may therefore use the same ‘closest Thursday’ approach as above, solving the end-of-year trickery at once. The question then becomes: how far from the first Thursday is the closest Thursday?

The question becomes even easier when we ask: which day of the year is the closest Thursday? Why? Because we can use a standard M function to answer this: the Day.DayOfYear function. This is the line of thinking:

• The closest Thursday is a number of weeks, or a multitude of seven days, away from the first Thursday.
• The ISO week number can therefore be calculated by dividing that distance by 7 and adding 1.
• The first Thursday of the year is between the 1^st and the 7^th of January (this must be, as the ISO year is equal to the calendar year).
• So, the day number of the closest Thursday is anywhere between 1 and 7 more than the distance from the first Thursday.
• But that means that dividing the day number by 7 and rounding up gives us the ISO week number; the rounding providing the +1 from the second bullet above.

In short, the M code for the above looks like this:

Number.RoundUp(Date.DayOfYear(ClosestThursday) / 7)

As a custom M function:

(DateInput as date) as number =>

let

    ClosestThursday = Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3),

    Weeks = Number.RoundUp(Date.DayOfYear(ClosestThursday) / 7)

in

    Weeks

That’s it!

The image below shows the results of these functions and some intermediate results for early 2023. The ‘Week of Year’ column contains the default Power Query week number results, which are obviously very different.