# How to calculate ISO week number in Power Query

I calculated ISO week number in Power Query following instructions from Wikipedia.
Here is how it goes. M code is at the end of this post.

It all starts with weekday calculation. By default Power Query Date.DayOfWeek function returns a number between 0 and 6 starting from Sunday. To return weekday number according to ISO8601 rules, you have to add optional first-weekday value and change numbering from 1 to 7. For this demonstration, I also generated a list of dates.

`=Date.DayOfWeek([Date], Day.Monday)+1`

After this little adjustment, it is possible to calculate ISO week number in Power Query with all necessary conditions.

Using ISO weekday numbers (running from 1 for Monday to 7 for Sunday), subtract the weekday from the ordinal date, then add 10. Divide the result by 7. Ignore the remainder; the quotient equals the week number. If the week number thus obtained equals 0, it means that the given date belongs to the preceding (week-based) year. If a week number of 53 is obtained, one must check that the date is not actually in week 1 of the following year.

```=if
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0

then
Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)

else if
(Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))

then
1

else
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)```

Looking for ISO year calculation? If you have ISO week numbering then here is the easiest way how to calculate ISO year in Power Query.

It might be a useful addition to a flexible calendar table that you can create with Power Query.

Here is the M code that you can put in Blank Query and try by yourself.

```//datacornering.com

let
Source = List.Dates(DateTime.Date(DateTime.LocalNow()), 365, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Weekday Number" = Table.AddColumn(#"Changed Type", "Weekday Number", each Date.DayOfWeek([Date], Day.Monday)+1),

#"ISO Week Number" = Table.AddColumn(#"Weekday Number", "ISO Week Number", each if
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0

then
Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)

else if
(Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))

then
1

else
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7))

in
#"ISO Week Number"```

Posted

in

### 24 responses to “How to calculate ISO week number in Power Query”

1. Carina Lindebjerg Larsen

This is just what I need! Can you also show how to add the proper year? based on the IOS column?

1. Janis Sturis

What do you mean with “proper year”? Please give an example.

1. Robert Maes

He means this:

Imagine a column like “Year – Weeknumber” > “2020 – 53”.
In 2021, the 1st of July fell on a Friday. This means that the 1st, 2nd and 3rd of January belong to week 53 of the previous year. These dates, from 2021, should thus fall under “2020 – 53”.
Consequently, the week of 04-01-2021 to 10-01-2021 is “2021 – 01”

1. Bruce Diepgrond

I looked it up, and every in my region (Europe) the first week of the year is the week with at least 4 days in the new year, as Robert explained (think the 1st of july was a type-o and should be 1st of january). Also our weeks start on a Monday, in some regions it sunday.

If you calculated the ISO week and had put it in a column [Week]
and calculated the Year and put it in a Column [Year]
and have the column with the Date [Date]
you can Calculate the ISO Year as follows:

DAX:
Iso Year = IF([Week]>51 && DAY([Date])<4,[Year]-1,IF([Week]28,[Year]+1, [Year]))

M:
if ([Week]>51 and Date.Day([Date])<4) then [Year]-1 else if ([Week]28) then [Year]+1 else [Year]

DAY([Date]) and Date.Day([Date]) tells you the day number of the month (1-31)

Hope this helps.

1. Bruce Diepgrond

Sorry, I see i pasted my M code wrongly

if ([Week]>51 and Date.Day([Date])<4) then [Year]-1 else if ([Week]28) then [Year]+1 else [Year]

2. Janis Sturis

3. Rob Light

Thank you for this Bruce, really helpful. Can you explain what the ‘else if ([Week]28)’ refers to? I can’t get it to work properly.

4. Prateeksha

Hi Bruce,

Is there supposed to be a symbol before the 28?

Your help is greatly appreciated

5. Bruce Diepgrond

I am sooooo incredibly sorry for leaving you guys blind for almost a year! I noted my mistake this morning and here’s the correction:

I hope this makes a lot more sense:

DAX:
Iso Year = IF([Week]>51 && DAY([Date])<4,[Year]-1,IF([Week]28,[Year]+1, [Year]))

M:
if ([Week]28) then [Year]+1

Thought process:
If the week number is smaller than 2 and the day of the month is greater than 28, this would imply that it would be week 1 and a date in december, so the year of the week should be +1

6. Bruce Diepgrond

For some reason this forum keeps cutting off my code! I sure hope this gets posted correctly no!
This:
([Week]28)
Should be replaced by this!
([Week]28)

Thought process:
If the week number is smaller than 2 and the day of the month is greater than 28, this would imply that it would be week 1 and a date in december, so the year of the week should be +1

Hello everyone, could someone provide me with the finished excel file? i download a csv file every week. this file has date, open, high, low and close columns. on the right i want to display the year, then the quarter, then the month, then the iso week! and on the far right the date. can someone provide me with such a file? i don’t know anything about programming and i think it wouldn’t be that much work. I can also provide you with the csv file if that helps

2. zedleb

Brilliant! Thank you so much Janis.

3. Sergio

hi,
I think you forgot a sign on: if ([Week]28)…

1. Khoi Pham

Great!

if ([Week]>51 and Date.Day([Date])51 and Date.Day([Date])<4) then [Year]-1 else [Year]

What are the limitations of not using the week28 comparison.

4. Prateeksha

Hi Khoi,
Is there a sign that should be before the 51?

5. Ken Edwards

For 2021 I have the first 3 days showing up as week 53. How do I modify the code to have these first 3 days show as week 1

1. Janis Sturis

That’s the normal result of ISO week numbers – every week should have 7 days.
https://en.wikipedia.org/wiki/ISO_week_date

It is not ideal and the one and the only way to number weeks.

Try to use Date.WeekOfYear function https://docs.microsoft.com/en-us/powerquery-m/date-weekofyear

2. Great

change monday -> sunday

6. Manasi

Hello

I do have same error of 53, weeks showing of first day of Feb as 53.

I used above query, but its showing week 5 and 6.

1. Janis Sturis

Try to use fuction Date.WeekOfYear function https://docs.microsoft.com/en-us/powerquery-m/date-weekofyear

Hello everyone, could someone provide me with the finished excel file? i download a csv file every week. this file has date, open, high, low and close columns. on the right i want to display the year, then the quarter, then the month then the iso week! I can provide the CVS file if it helps.

8. Natalie Riis Damstrup

Hi,

How do you implement this adjustment if you have a start and an end date for each row?
I want to split an amount across weeks within 2021 determined by a range of days from start to end of period.

Thanks!

9. Krzysztof Janczura

Howdy! Careful with the year… Check out the below.

let
UTCDate = DateTimeZone.UtcNow()
in
(if Date.DayOfWeek(UTCDate,Day.Thursday) < 4
else Number.ToText(Date.Year(UTCDate)))
&
"w"
&
Number.ToText(
if Number.RoundDown((Date.DayOfYear(UTCDate)-(Date.DayOfWeek(UTCDate, Day.Monday)+1)+10)/7)=0
then Number.RoundDown((Date.DayOfYear(#date(Date.Year(UTCDate)-1,12,31))-(Date.DayOfWeek(#date(Date.Year(UTCDate)-1,12,31), Day.Monday)+1)+10)/7)
else if (Number.RoundDown((Date.DayOfYear(UTCDate)-(Date.DayOfWeek(UTCDate, Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year(UTCDate),12,31), Day.Monday)+1<4))
then 1
else Number.RoundDown((Date.DayOfYear(UTCDate)-(Date.DayOfWeek(UTCDate, Day.Monday)+1)+10)/7)
),
2,
"0"
)

10. Bruce Diepgrond

Still doesn’t work i added some spaces:
( [Week] 28)