Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Calendars can be a real pain when analyzing your numbers. Years can start on any date and end on any date. That means it’s very likely you end up with a partial week at the start and end of the year. But alas, they invented something for this.

The ISO Week and ISO Year. The ISO Week system makes it easy to categorize your dates into weeks. And the best part is, you can easily include them in your Calendars in Power Query. In this article, I’ll give you a solid understanding of what they are, and then I’ll show you step-by-step how to create ISO Week numbers and ISO Year numbers in Power Query.

Table of contents

Introduction

ISO calendars are a calendar system based on the International Organization for Standardization (ISO) standard. These calendars are incredibly handy for businesses and organizations operating globally, as they offer a consistent approach to handling dates and times across various countries and cultures.

In data analysis, consistency is crucial, and ISO calendars play a vital role in maintaining that consistency. By representing dates and times in a standardized manner, you can easily compare data across diverse regions, time zones, and even industries. This not only simplifies the analysis process but also helps you sidestep any potential date-related issues that might arise due to differing calendar systems.

Understanding ISO Calendar Basics

Definition of ISO Week and Year

ISO Week Numbers are a way of specifying which week of the year it is. They’re based on a standard called ISO 8601 and are often used for fiscal calendars.

An ISO week is a seven-day period that starts on Monday and ends on Sunday. The first ISO week of the year is the one that contains the first Thursday of the year. Similarly, the ISO year is the one that has the majority of days in that first ISO week.

This means that the first week can have days from December of the last year and days from January of the current year. Because of this system, each year has either 52 or 53 weeks. The years that have 53 weeks are the ones where the first day of the year is a Thursday or when it’s a leap year and the first day is on a Wednesday.

Difference from Regular Calendar

You might be wondering how ISO weeks and years are different from regular calendar weeks and years. Well, the main difference is that ISO weeks always start on Monday, while regular calendar weeks can start on different days depending on the calendar system being used. Also, the definition of the first ISO week is different from the first calendar week, as mentioned earlier.

Advantages to an ISO Week

So by adopting an ISO Calendar, the advantages include:

  • Weeks always have 7 days. There’s no such thing as a partial week.
  • Each week belongs to just one year.
  • All ISO Years start on a Monday and end on a Sunday.
  • All years are the same, except for some years having an extra week 53 at the end.

Creating an ISO Calendar in Power Query

Now that we have a good understanding of ISO calendar basics, it’s time to create our very own ISO calendar in Power Query. I’ll guide you through the process step by step!

Create ISO Week Number

Currently, Power Query does not have a native ISO Week or ISO year function available. So, how can you generate ISO Week Numbers in Power Query?

Step 1: Current Thursday

The first thing we need to do to create an ISO week number column is to find the Thursday of the current week. This can be done using the Date.DayOfWeek function and the Date.AddDays function in Power Query.

Important

The Date.DayOfWeek function will give us the day of the week for a certain date. The first day of the week is represented by 0 in the Date.DayOfWeek function. And because Thursday is the 4th day of the week, we’ll use 3 as our base value.

To shift the current date to the Thursday of that week, we’ll subtract the number of days away from Thursday. We can subtract the value returned by Date.DayOfWeek( [Date], Day.Monday) from our base value of 3.

The result will be the number of days we need to add to the current date to arrive on the Thursday of this week. By combining the Date.DayOfWeek function and Date.AddDays function, we’ll be able to find the Thursday of the current week in Power Query.

CurrentThursday = 
   Date.AddDays( 
       [Date],                                
       3                   
       - Date.DayOfWeek( [Date], Day.Monday) 
)  
Calculate Current Thursday of Week in Power Query

Step 2: Year of Current Thursday

Now that we’ve found the Thursdays in our data, the next step is to figure out which year each Thursday belongs to. We can use the Date.Year function to do this. This function will give us the year for each Thursday date we have.

It’s really easy to use. All you need to do is reference the result of the previous calculation. That way, you’ll get a new column with the year of each Thursday.

YearCurrentThursday =
   Date.Year( [CurrentThursday] )
Calculate Year of Current Thursday in Power Query

Step 3: First Thursday of Year

Next, we need to retrieve the first Thursday of the year. The easiest way to do that is by taking January 7th of the current year. Then count days as if the week starts on Friday and find out what the Day Of Week number is of the 1st of January of that year.

If the week starts on a Friday, the Date.DayOfWeek function will act like Thursday is the last day of the week. Now subtract that Day of Week value from January 7th of the current year. You now retrieve the first Thursday of the year.

Okay, so now that we have the year of each Thursday, we’re going to find the first Thursday of the year. And the easiest way to do that is by using January 7th of the current year as a reference point.

We’ll count days as if the week starts on Friday, and find out what the Day Of Week number is of the 1st of January of that year. Now, if the week starts on a Friday, the Date.DayOfWeek function will act like Thursday is the last day of the week. This means that we can use that Day of Week value to subtract from January 7th of the current year. By doing so, we’ll be able to find the first Thursday of the year.

FirstThursdayOfYear = 
   Date.AddDays( 
      #date( [YearCurrentThursday],1 ,7 ),
      - Date.DayOfWeek( #date( [YearCurrentThursday],1 , 1), Day.Friday ) 
   )  
Return First Thursday of Year in Power Query

With these pieces, you have enough to compute the ISO Week number of the year

Step 4: Compute ISO Week Number

The last step is to compute the ISO week number. To get to an ISO Week number for your calendar, all you have to do is the following.

  1. Count the number of days between Thursday in the current week and the first Thursday of the year.
  2. Divide this number of days by 7
  3. Add 1 to the previous result.
ISO Week = 
   ( Duration.Days( [CurrentThursday] - [FirstThursdayOfYear] ) 
        / 7 )
      + 1
Create ISO Week Number of Year in Power Query

All Steps Combined

For those who want to quickly create an ISO Week Number column in Power Query without reading the manual, this chapter is for you. Below, you can find the final code that you can use to calculate an ISO Week Number column in one step. Just paste it into your custom column formula box, and you’re good to go!

let
  CurrentThursday =  Date.AddDays( [Date], 3 - Date.DayOfWeek( [Date], Day.Monday ) ), 
  YearCurrThursday = Date.Year( CurrentThursday ), 
  FirstThursdayOfYear = 
        Date.AddDays( #date(YearCurrThursday, 1, 7) , 
                    - Date.DayOfWeek( #date( YearCurrThursday, 1, 1 ), Day.Friday ) ), 
  ISO_Week = Duration.Days( CurrentThursday - FirstThursdayOfYear ) / 7 + 1
in
  ISO_Week

And that’s it. With this simple code, you can easily add an ISO Week number to your Power BI Reports.

Create ISO Year Number

So now you have an ISO Week calculation, it would also be fitting to create an ISO Year number for your fiscal calendar. The easiest way to create an ISO Year from an ISO Week Number is

= Date.Year( 
      Date.AddDays( [Date], 26 - [ISOWeekNumber] )
)
Calculate ISO Year in power Query

If you don’t have an ISO Week Number, you can compute the ISO Year by using:

let
   CurrentThursday =  Date.AddDays( [Date], 3 - Date.DayOfWeek( [Date], Day.Monday ) ),
   YearCurrThursday = Date.Year( CurrentThursday )
in YearCurrThursday

Create Current ISO Week

Have you ever needed to return the ISO Week number that corresponds to the current date? Well, it’s actually pretty easy to do with Power Query! All you need is a table that has a Date column and an ISO Week column.

Once you have those columns in place, you can use the following code to return the Current ISO Week:

Current ISO Week Number

Let me break it down for you.

#"Add ISO Week"

First, the code references the previous step name in your query, which is #”Add ISO Week”. This returns a table that includes all of the columns in your date table, including the ISO Week column.

#"Add ISO Week"{ [Date = Date.From(DateTime.LocalNow())] }

Next, the code uses the { [ Column = x ] } construct to search for a row in that table that matches the current date. This match has to return a unique row to be valid.

The row it returns contains all columns in your date table, including the ISO Week.

#"Add ISO Week"{ [Date = Date.From(DateTime.LocalNow())] }[ISO Week]

Finally, by including the [ISO Week] between square brackets at the end of the formula, Power Query will return the current ISO Week number.

Add Current ISO Week to Table

And that’s it! With just a few lines of code, you can easily return the ISO Week that corresponds to the current date in Power Query.

ISO Calendar: Free Script

To put everything together, I’ve created a script. With this script, you can quickly generate a table with ISO Week and Year numbers for any date range you specify. Just paste it into the advanced editor, and you’re good to go!

let
    Today = Date.From( DateTime.LocalNow() ),
    StartDate = #date(2022, 1, 1),
    EndDate = #date(2024, 1, 1),
    #"List of Dates" = List.Dates( StartDate, Duration.Days( EndDate - StartDate ) +1, #duration( 1, 0, 0, 0 ) ),
    #"Converted to Table" = Table.FromList( #"List of Dates", Splitter.SplitByNothing(), type table[Date = Date.Type] ),
    #"Add ISO Week" = Table.AddColumn(#"Converted to Table", "ISO Week", each let
  CurrentThursday =  Date.AddDays( [Date], 3 - Date.DayOfWeek( [Date], Day.Monday ) ),
  YearCurrThursday = Date.Year( CurrentThursday ),
  FirstThursdayOfYear =
        Date.AddDays( #date(YearCurrThursday, 1, 7) ,
                      - Date.DayOfWeek( #date( YearCurrThursday, 1, 1 ), Day.Friday ) ),
  ISO_Week = Duration.Days( CurrentThursday - FirstThursdayOfYear ) / 7 + 1
in
  ISO_Week, Int64.Type ),
    #"Add Current ISO Week" = Table.AddColumn(#"Add ISO Week", "Current ISO Week", each 
#"Add ISO Week"{ [Date = Date.From( DateTime.LocalNow() ) ]} [ISO Week], 
Int64.Type ),
    #"Added Custom" = Table.AddColumn(#"Add Current ISO Week", "ISO Year", each 
Date.Year( 
      Date.AddDays( [Date], 26 - [ISO Week] ) ), 
      Int64.Type )
in
    #"Added Custom"

Conclusion

And that’s it! You have just learned a simple yet effective way to create ISO Week and Year numbers using Power Query, following the ISO 8601 standard. With this new knowledge, you no longer have to deal with inconsistent weeks.

Now, you won’t have to deal with those inconsistent weeks anymore! By using ISO Week and Year numbers in your Power BI reports, you’ll have consistent and accurate data comparisons.

Tip

Enjoyed these Date-related calculations? You may also be curious about how to create a Calendar that supports Multiple Languages.

That’s all for now. Happy querying!

Share on:

Latest from my blog

  1. Hi, thank you so much, it was really fun to do it 🙂 You are really good at it, it helped me to upgrade my skills.
    but wouldn’t it be the same if just:

    ISO Week = Date.WeekOfYear ( [Date] ) – 1

    Just -1 of PQ week function..
    For me it worked. I checked with basic excel function isoweek, and I need Monday to be first.

    What do you think, is it correct? 🙂

    Reply
  2. Hi Rick,

    Thanks for the explanation and the short code provided. I have been using a ISO week function for awhile which I came across on another blog and has been working well. This explanation is really useful and I like the readable short code!

    The reason I came across this is because I was looking for a solution for a problem I just came across with using the IsInPreviousNWeek function in the following method.

    = Date.IsInPreviousNWeeks(Date.AddDays(DateTime.FixedLocalNow(), -7), 2)

    I thought this was working great until I noticed some discrepancies in my data and realized this must be using calendar week rather than Iso Week.

    Is it possible to use this function with Iso Week do you know?

    Look forward to your next video, it’s been awhile 🙂

    Regards

    Erik

    Reply
    • Thanks Erik, hope to work on some more videos soon 🙂

      What exactly are you trying to achieve with the code? Am I reading it correctly as in:
      1. it grabs the current date.
      2. moves it back 7 Days.
      3. checks whether that date is in the last 2 weeks.

      I may be wrong, but it sounds to me like this always returns ‘true’ as a result. Is that the issue? And what is your desired result

      Reply
      • Hi Rick,

        Thanks for your response. The way you formulated your response made me rethink about it and it seems I was overthinking this.

        You are correct, it does bring back true as a result which is what I needed but aligned it to incorrect fields causing a mismatch in data.

        My end goal here is to limit the data a user has to select in order to get what needs to be generated. For example, for certain reports, they only need to have a selection for the last 2 weeks and not of 6 months worth of weeks.

        Thanks again, with your response I was able to align it correctly. Apologies for the confusion. 🙂

        Regards

        Erik

        Reply
  3. Thanks wholeheartedly for your excellency –
    in this post, but in all your posts !
    Very useful to go into more depths and inspiring too for my own courses.

    Reply
    • Wow, thanks so much for the kind words! I’m glad that you found my post on ISO week numbers to be useful and that it went into more depth.

      It’s always great to hear that my posts are inspiring your courses. If you have any other questions or topics you would like me to cover in the future, please let me know! And good luck with your own courses!

      Reply
  4. I am not a Power Query person (I know, “Boo!”), so I do not know if this can easily be encoded in Power Query or not. Let me first mention that the following is not original with me, but unfortunately, I do not have a reference for where I first saw it. A little history first. The WEEKNUM function in Excel is not always accurate… old versions of Excel use to report both January 1st and January 2nd in 2101 as weeks 52 and 53 respectively, newer versions of Excel are reporting weeks 1 and 2 for both of them… the actual ISO week number for those two dates is 52. Somewhere in the distant past, someone posted this VBA function to calculate the actual ISO week number for any date from 1/1/100 to 12/31/9999 (of course, somewhat meaningless for dates prior to the adoption of the Gregorian calendar)…

    Function WkIso(d)
    WkIso = ((((d+692501)\7 Mod 20871)*28+4383) Mod 146096 Mod 1461)\28+1
    End Function

    Here is how I encoded this as an Excel formula (assuming the date is in cell A1)…

    =INT(MOD(MOD(28*MOD(INT((A1+692501)/7),20871)+4383,146096),1461)/28)+1

    If you can encode either of these into Power Query, this should make your function somewhat shorter.

    Reply
    • Rick,

      That’s a spectacularly short code. You always manage to come up with the shortest and most efficient codes. I know that Power Query can be a bit more verbose, but it still works great. For example, the code you provided is perfect and written like:

      Number.IntegerDivide(
        Number.Mod(
          Number.Mod(
            28 * Number.Mod(
                    Number.IntegerDivide((Number.From([Date]) + 692501) / 7, 1), 
                    20871 ) + 4383, 
            146096
          ), 
          1461
        )
          / 28, 
        1
      )
        + 1

      It’s really up to personal preference whether one prefers the short or the readable one. Thanks for sharing the code!

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.