Create ISO Week and ISO Year in Power Query M (ISO 8601)

Calendars can be a real pain when analyzing your numbers. Years can start and end on any day of the week. That means it’s very likely you end up with a partial week at the start and end of the year. But luckily, 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. Next I’ll show you how to create ISO Week numbers, ISO Year numbers and other related fields in Power Query.

This article is part of the Date Table series, which consists of:

Here’s an overview of what we’ll cover in this article:

Table of contents

• 1. Understanding the ISO Calendar
• 2. Creating ISO Week Related Calculations
  • 2.1 Create ISO Week Number
  • 2.2. Other ISO Week Fields
• 3. Creating ISO Year Related Calculations
  • 3.1. Creating ISO Year Number
  • 3.2. Creating Start and End of ISO Year
  • 3.3. Create ISO Year Offset
• 4. Creating ISO Quarter Related Calculations
  • 4.1. Creating ISO Quarter Number
  • 4.2. Creating Start and End of ISO Quarter
  • 4.3. Creating ISO Day of Quarter
  • 4.4 Creating ISO Quarter Offset
  • 4.5. Creating ISO Quarter Labels
• 5. ISO Calendar: Free Script
• 6. Conclusion
• 7. Frequently Asked Questions

1. Understanding the ISO Calendar

ISO calendars are a calendar system based on the International Organization for Standardization (ISO) standard. These calendars are handy as they offer a consistent approach to handling dates and times across various countries and cultures. So what are ISO weeks and Years?

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.

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. We will look at how to create the following ISO calendar related fields:

2. Creating ISO Week Related Calculations

The ISO week is the most important component in your ISO calendar. Other calculations are often dependent on it. Here’s an overview of the ISO week related fields we will create:

So how do you create these?

2.1 Create ISO Week Number

Currently, Power Query does not have a built-in function for generating ISO week numbers or ISO years. However, you can create ISO week numbers using a series of steps.

Step 1: Identify the Current Thursday

The first step in creating an ISO week number column is to find the Thursday of the current week. This is achieved using the Date.DayOfWeek and Date.AddDays functions.

To shift the current date to the Thursday of the same week, follow these steps:

1. Calculate the Day of the Week: Use Date.DayOfWeek([Date], Day.Monday) to determine the day of the week for the current date.
2. Determine the Days to Add: Subtract the day of the week from 3. This will give the number of days needed to add to the current date to reach Thursday.

To find the Thursday of the current week, you can combine Date.DayOfWeek and Date.AddDays as follows:

Date.AddDays( 
  Date],                                
  3                   
  - Date.DayOfWeek( [Date], Day.Monday) 
)  

In this expression:

• Date.AddDays shifts the date to the desired day (Thursday).
• 3 - Date.DayOfWeek([Date], Day.Monday) calculates the number of days to add, effectively adjusting the current date to Thursday of the same week.

The result looks as follows:

Step 2: Determine the Year of the 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.

To use it, you simply reference the result from the previous step (the calculated Thursday) to get the year. Here’s how:

Date.Year( [CurrentThursday] )

Step 3: Determine the First Thursday of the Year

Next, we need to retrieve the first Thursday of the year. We can do that by performing the following steps:

1. Set Reference Date: Use January 7th of the current year as the reference point.
2. Calculate the Day of the Week for January 1st: Determine the day of the week for January 1st of the current year using Date.DayOfWeek.
3. Adjust the Date: Subtract the day of the week value (considering the week starts on Friday) from January 7th to find the first Thursday of the year.

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.

The logic to return the first thursday of the year is as follows:

Date.AddDays(
  #date( [YearCurrentThursday],1 ,7 ),
  - Date.DayOfWeek( #date( [YearCurrentThursday],1 , 1), Day.Friday ) 
 )  

In this code:

• #date([YearCurrentThursday], 1, 7) sets the reference date to January 7th of the current year.
• Date.DayOfWeek(#date([YearCurrentThursday], 1, 1), Day.Friday) calculates the day of the week for January 1st, assuming the week starts on Friday.
• Date.AddDays adjusts January 7th backward by the number of days calculated to find the first Thursday of the year.

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

Step 4: Compute ISO Week Number

The final step is to calculate the ISO week number. This involves determining the number of days between the Thursday of the current week and the first Thursday of the year, then converting that duration into weeks.

The steps to do this are:

1. Calculate the Days Between Thursdays: Determine the number of days between CurrentThursday and FirstThursdayOfYear using the Duration.Days function.
2. Convert Days to Weeks: Divide the number of days by 7 to convert it into weeks.
3. Adjust the Week Number: Add 1 to the result to get the ISO week number.

Here’s the Power Query formula to compute the ISO week number:

ISO Week = 
   ( Duration.Days( [CurrentThursday] - [FirstThursdayOfYear] ) 
        / 7 )
      + 1

In this code:

• Duration.Days([CurrentThursday] - [FirstThursdayOfYear]) calculates the number of days between the two Thursdays.
• Dividing by 7 converts these days into weeks.
• Adding 1 adjusts the result to get the correct ISO week number.

The following image shows the result of this calculation:

2.2. Other ISO Week Fields

You just learned how to create an ISO week. However, when combining these values with a regular calendar, it’s can be hard to distinguish between a regular week and an ISO week. After all, a number is just a number. It is therefore be helpful to add some fields that make it more explicit we’re dealing with ISO weeks.

If we could show ISO week 4 as “ISO W04”, and a regular week as “W04”, it’s easy for the user to know what they’re dealing with.

You can create an ‘ISO Week Label‘ as follows:

"ISO W" & Text.PadStart( Text.From( [ISO_Week] ), 2, "0")

For most of us, it’s also hard to remember what dates we’re dealing with when we see only a week number. I find it useful to add a field that includes both the week number, the start and the end date of a week. ISO W04 can be represented as: ‘ISO W04: 22 jan 2024 - 28 jan 2024‘. Here’s the code to create an ISO WeekRange Label:

[ISO_WeekLabel] 
& ": " 
& Date.ToText( [StartOfWeek], "d MMM yyyy" ) 
& " - " 
& Date.ToText( [EndOfWeek], "d MMM yyyy" )

Lastly, it’s also useful to add a field that combines the ISO year with the ISO weeknumber. That could be ISO 2024 W04 for our last example. Here’s how you create the ISOYearWeekLabel:

[ISO_YearLabel] & " W" & Text.PadStart( Text.From( [ISO_Week] ), 2, "0" )

Here’s an image that compares a table with ISO labels to a table without. See how much clearer the left one is?

3. Creating ISO Year Related Calculations

Besides weeks, the ISO year related calculations are the next ones we will focus on. This section will show you how to create the following columns:

So how do we create these?

3.1. Creating ISO Year Number

Now that you have the ISO week calculation, it’s also useful to create an ISO year number for your fiscal calendar. Here’s how you can do it:

The easiest way to create an ISO Year is by using the ISO week number as follows:

Date.Year( 
  Date.AddDays( [Date], 26 - [ISOWeekNumber] )
)

In this code:

• Date.AddDays([Date], 26 - [ISOWeekNumber]) adjusts the date by 26 days minus the ISO week number.
• Date.Year extracts the year from this adjusted date.

To easily distinguish between the regular year number and an ISO year number, I recommend adding the ISO Year Label field. This field prefixes the year with the text ‘ISO’. You can add it with the following expression:

"ISO " & Text.From( [ISO_Year] )

3.2. Creating Start and End of ISO Year

Since an ISO year can start and end on different dates, it’s often useful to have a field that tells you when.

To create an ISO Start of Year field you can use:

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)),
  StartOfISOYear = Date.StartOfWeek( FirstThursdayOfYear )
in
  StartOfISOYear 

Returning the ISO End of Year is complex. In the free calendar script I took the approach of generating a list of dates of the 1st of June for all dates between start and end date of the dataset. I then find the ISO Start Of Year of each. Then to find the End of Year, I filter this list to be bigger than the ISO Start of Year of the current row. By taking the first remaining item in the list and subtracting 1 from it, you get the ISO End of Year. Due to the length of the code, I have left it out from this section.

You now have the date that belongs to the last day of the ISO year.

3.3. Create ISO Year Offset

To wrap up the year calculations, what remains is to add an ISO year offset calculation. This value represents the number of ISO years from the current year. Here 0 represents the current ISO year, whereas -1 the previous and 1 the next ISO Year. You can use the value to make easy time-intelligence calculations or to filter your calendar table to only show particular dates.

Here’s how to create the ISO Year offset value:

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 ) ),
  DaysDifference = 
    Duration.Days( CurrentThursday  - FirstThursdayOfYear  ),
  WeekNumber = ( DaysDifference / 7 ) + 1,
  ISOYear= Date.Year( Date.AddDays( [Date], 26 - WeekNumber)),
  CurrentISOYear = 
    Date.Year( Date.AddDays( Date.From( DateTime.LocalNow() ), 26 - WeekNumber)),
  ISOYearOffset = ISOYear - CurrentIsoYear
in
  ISOYearOffset 

This code works independently of other steps. However, a lot of the variables in this expression are also used elsewhere in the ISO calendar. So the final script for an ISO calendar will re-use the fields.

3.4. Create ISO YearRangeLabel

Since the ISO Year can start and end on irregular dates, it can be useful to add a date range label to it. The easiest way to do that is:

ISO_YearLabel] & ": " 
& Date.ToText( [ISO_StartOfYear], "d MMM yyyy" ) & " - " 
& Date.ToText( [ISO_EndOfYear], "d MMM yyyy" )

4. Creating ISO Quarter Related Calculations

When we look at our data, it can also be useful to look at quarters. Since the starting date of the ISO year can move, so can the date ranges of each quarter. In this section we delve into the different ISO quarter related fields you can generate. Here’s an overview of the columns we will look at:

Let’s find out how to create them!

4.1. Creating ISO Quarter Number

The first quarter column you will create is the ISO quarter number. To do that, we can make use of the ISO Week number we calculated earlier.

You can return the ISO Quarter number with the following formula:

Number.RoundUp( List.Min( { [ISO_Week], 52 } ) / 13 )

In this example, we divide the ISO Week number by 13 and round up the result to the nearest integer. The calculation works with a maximum of week 52, so that week 53 will receive a quarter number of 4.

4.2. Creating Start and End of ISO Quarter

Next, it is useful to know the start and end dates of a quarter. We can make use of the ISO_Quarter number to compute this. Here’s how you can compute the ISO start of quarter date:

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_StartOfYear = Date.AddDays( FirstThursdayOfYear , ( [ISO_Quarter] - 1 ) * 13 * 7  - 3 )
in
  ISO_StartOfYear 

For the ISO end of quarterdate, we need to make sure it also covers for years with 53 weeks. To do that, you can make use of the ISO End of year date, together with the quarter number. Here’s how you can do it:

if [ISO_Quarter] = 4 then [ISO_EndOfYear] 
else Date.AddDays( [ISO_StartOfQuarter], 7 * 13 -1 )

This code sets the ISO end of quarter date equal to the ISO end of year date for Q4, and otherwise uses the ISO Start of Quarter date shifted forward by 13 weeks.

4.3. Creating ISO Day of Quarter

To return day of quarter number, you can use an easy expression:

Number.From( [Date] - [ISO_StartOfQuarter]  ) +1

Here we retrieve the number of days since the start of the ISO quarter.

4.4 Creating ISO Quarter Offset

Using an offset value relative to the current period can be useful for time intelligence calculations. This is also the case for ISO quarters.

To create an ISO Quarter offset you can use:

let
  // The rounding method for returning ISO quarters only works for 52 weeks. 
  // When the current week is 53, pretend like it is 52
  MaxISOWeek = List.Min( { CurrentIsoWeek, 52} ),
  ISO_QuarterOffset= 
    ( ( [ISO_Year] - CurrentIsoYear ) * 4 ) 
    + ( [ISO_Quarter] - Number.RoundUp( List.Min( { MaxISOWeek } ) / 13) )
in
  ISO_QuarterOffset

4.5. Creating ISO Quarter Labels

The ISO Quarter label makes it easier to distinguish between regular quarters and ISO quarter. You can create one by using:

"ISO Q" & Text.From( [ISO_Quarter] )

To create the YearQuarterLabel can be created using:

[ISO_YearLabel] & " Q" & Text.From([ISO_Quarter]

5. 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
    /* _____________ The following Values are used for computing ISO Weeks _______________*/
    FxCurrentThursday = (DateValue as date ) as date => Date.AddDays( DateValue, 3 - Date.DayOfWeek( DateValue, Day.Monday)),
    FxFirstThursdayOfYear = 
      (DateValue as date ) as date => [ 
          CurrentThursday = FxCurrentThursday( DateValue ),
          YearCurrThursday = Date.Year( CurrentThursday ),
          FirstThursdayOfYear = Date.AddDays( #date( YearCurrThursday, 1, 7), - Date.DayOfWeek( #date( YearCurrThursday, 1, 1), Day.Friday))
        ][FirstThursdayOfYear],
    FxIsoYear = 
      ( DateValue as date ) as number => [
          DaysDifference = Duration.Days( FxCurrentThursday(DateValue) - FxFirstThursdayOfYear(DateValue ) ),
          WeekNumber = ( DaysDifference / 7 ) + 1,
          ResultYear = Date.Year( Date.AddDays( DateValue, 26 - WeekNumber))
        ][ResultYear],
    CurrentIsoYear = FxIsoYear( Today ),
    FxIsoWeek = ( DateValue as date ) as number =>  Duration.Days(FxCurrentThursday(DateValue) - FxFirstThursdayOfYear( DateValue )) / 7 + 1,
    CurrentIsoWeek = FxIsoWeek( Today ),
    ISOStartYears = 
      [ StartYear = Date.Year( StartDate ),
  NumberOfYears = Date.Year( EndDate) - StartYear +1,
  Years =  { 0..NumberOfYears },
  Dates = List.Transform( Years, each #date( StartYear + _, 6, 1  ) ),
  ISOStartYears = List.Buffer( List.Transform( Dates, each Date.StartOfWeek( FxFirstThursdayOfYear( _ ) ) ) )
] [ISOStartYears],
    ______________________StartCalendar___________ = ISOStartYears,

/* _________________________________________________________________________________________________ */


    Today = Date.From(DateTime.LocalNow()),

    // Sets the start date for your calendar
    StartDate = #date(2023,1,1 ),

    // The calendar runs until the end of the current year. Change this if you need your calendar to run to another date. 
    EndDate =  Date.EndOfYear(Today)+#duration( 365,0,0,0),
    ListOfDates = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
    MyDateTable = Table.FromList(ListOfDates, Splitter.SplitByNothing(), type table[Date = date], null, ExtraValues.Error),
    Add_DateAsInteger = Table.AddColumn(MyDateTable, "DateAsInteger", each Number.From(Date.ToText([Date], "yyyyMMdd")), Int64.Type),
    Add_StartOfWeek = Table.AddColumn(Add_DateAsInteger, "StartOfWeek", each Date.StartOfWeek([Date], Day.Monday), Date.Type),
    Add_EndOfWeek = Table.AddColumn(Add_StartOfWeek, "EndOfWeek", each Date.EndOfWeek([Date]), Date.Type),
    /* Start ISO Calendar Fields. Some of these make use of the functions defined at the start of this query */    
    Add_ISO_Year = Table.AddColumn( Add_EndOfWeek, "ISO_Year", each FxIsoYear([Date]), Int64.Type),
    Add_ISO_YearLabel = Table.AddColumn(Add_ISO_Year, "ISO_YearLabel", each "ISO " & Text.From( [ISO_Year] ), Text.Type),
    Add_ISO_YearDefault = Table.AddColumn(Add_ISO_YearLabel, "ISO_YearDefault", each if [ISO_Year] = CurrentIsoYear then "Current" else if [ISO_Year] = (CurrentIsoYear - 1) then "Previous" else [ISO_YearLabel], Text.Type),
    Add_ISO_StartOfYear = Table.AddColumn(Add_ISO_YearDefault, "ISO_StartOfYear", each Date.StartOfWeek( FxFirstThursdayOfYear([Date] ) ), Date.Type),
    Add_ISO_EndOfYear = Table.AddColumn(Add_ISO_StartOfYear, "ISO_EndOfYear", (x)=> List.First( List.Select( ISOStartYears, each _ > x[ISO_StartOfYear] ) ) - #duration(1,0,0,0), Date.Type),
    Add_ISO_YearRangeLabel = Table.AddColumn( Add_ISO_EndOfYear, "ISO_YearRangeLabel", each [ISO_YearLabel] & ": " & Date.ToText( [ISO_StartOfYear], "d MMM yyyy" ) & " - " & Date.ToText( [ISO_EndOfYear], "d MMM yyyy" ), type text ),
    Add_ISO_Week = Table.AddColumn(Add_ISO_YearRangeLabel, "ISO_Week", each FxIsoWeek([Date]), Int64.Type),
    Add_ISO_WeekLabel = Table.AddColumn(Add_ISO_Week, "ISO_WeekLabel", each "ISO W" & Text.PadStart( Text.From( [ISO_Week] ), 2, "0" ), Text.Type),
    Add_ISO_WeekRangeLabel = Table.AddColumn(Add_ISO_WeekLabel, "ISO_WeekRangeLabel", each [ISO_WeekLabel] & ": " & Date.ToText( [StartOfWeek], "d MMM yyyy" ) & " - " & Date.ToText( [EndOfWeek], "d MMM yyyy" ), type text ),
    Add_ISO_YearWeekLabel = Table.AddColumn(Add_ISO_WeekRangeLabel, "ISO_YearWeekLabel", each [ISO_YearLabel] & " W" & Text.PadStart( Text.From( [ISO_Week] ), 2, "0" ), Text.Type),
    Add_ISO_Quarter = Table.AddColumn(Add_ISO_YearWeekLabel, "ISO_Quarter", each Number.RoundUp( List.Min( { [ISO_Week], 52 } ) / 13 ), Int64.Type ),
    Add_ISO_QuarterLabel = Table.AddColumn(Add_ISO_Quarter, "ISO_QuarterLabel", each "ISO Q" & Text.From([ISO_Quarter]), Text.Type),
    Add_ISO_WeekOfQuarter = Table.AddColumn(Add_ISO_QuarterLabel, "ISO_WeekOfQuarter", each [ISO_Week] - (( [ISO_Quarter] -1 ) * 13 ), Int64.Type ),
    Add_ISO_YearQuarterLabel = Table.AddColumn(Add_ISO_WeekOfQuarter, "ISO_YearQuarterLabel", each [ISO_YearLabel] & " Q" & Text.From([ISO_Quarter]), Text.Type),
    Add_ISO_YearOffset = Table.AddColumn(Add_ISO_YearQuarterLabel, "ISO_YearOffset", each [ISO_Year] - CurrentIsoYear, Int64.Type),
    Add_ISO_QuarterOffset = Table.AddColumn(Add_ISO_YearOffset, "ISO_QuarterOffset", each (([ISO_Year] - CurrentIsoYear) * 4) + ([ISO_Quarter] - Number.RoundUp( List.Min( {CurrentIsoWeek, 52} ) / 13)), Int64.Type),
    Add_ISO_TotalWeeks = Table.AddColumn(Add_ISO_QuarterOffset, "ISO_TotalWeeks", each Number.RoundUp( Duration.Days( [ISO_EndOfYear] - [ISO_StartOfYear] ) / 7 ), Int64.Type ),
    Add_ISO_StartOfQuarter = Table.AddColumn(Add_ISO_TotalWeeks, "ISO_StartOfQuarter", each Date.AddDays( FxFirstThursdayOfYear([Date]), ([ISO_Quarter] - 1) * 13 * 7  - 3 ), type date ),
  // To make ISO end of quarter dates for years with 53 weeks show correctly, we use the ISO_EndOfYear calculation
  Add_ISO_EndOfQuarter = Table.AddColumn(Add_ISO_StartOfQuarter, "ISO_EndOfQuarter", each if [ISO_Quarter] = 4 then [ISO_EndOfYear] else Date.AddDays( [ISO_StartOfQuarter], 7 * 13 -1 ), type date ),
    Add_ISO_DayOfQuarter = Table.AddColumn(Add_ISO_EndOfQuarter, "ISO_DayOfQuarter", each Number.From( [Date] - [ISO_StartOfQuarter]  ) +1, Int64.Type ),
    Add_ISO_DayOfYear = Table.AddColumn(Add_ISO_DayOfQuarter, "ISO_DayOfYear", each Number.From( [Date] - [ISO_StartOfYear]  ) +1, Int64.Type ),
    Add_ISO_QuarterRangeLabel = Table.AddColumn(Add_ISO_DayOfYear, "ISO_QuarterRangeLabel", each "ISO Q" & Text.From( [ISO_Quarter] ) & ": " & Date.ToText( [ISO_StartOfQuarter], "d MMM yyyy" ) & " - " & Date.ToText( [ISO_EndOfQuarter], "d MMM yyyy" ), type text ),
    // Renames all columns so they have spaces between words. Also removes underscores.
    #"Rename Columns" = Table.TransformColumnNames(Add_ISO_QuarterRangeLabel, each [
  SplitTextByTransition =  Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})(_), 
  CombineValues = Text.Combine( SplitTextByTransition, " " ), 
  RemoveUnderscores = Text.Replace( CombineValues, "_", " ") ][RemoveUnderscores] )
in
    #"Rename Columns"

6. 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.

That’s all for now. Happy querying!

7. Frequently Asked Questions