Week Number and ISO Week Number in Power Query

[GopVan]

I have added a week number column in Power Query and the output was as follows:

26/12/2022 - Week 53

...

31/12/2022 - Week 53

01/01/2023 - Week 1

02/01/2023 - Week 2

What is the logic behind this numbering? Why does Week 1 include only Jan 1, 2023?

How can I generate the ISO Week Number in Power Query (DAX) such that Jan 2, 2023, is Week 1?

[ccanetta]

This is the equivalent to ISOWEEKNUM (or WEEKNUM 21) that worked for me --> Date.DayOfWeek([Date], Day.Thursday)-1

[Pepijn]

A shorter formula for ISO-weeks in PBI:

Number.Round((Date.DayOfYear(Date.AddDays([Date],3-Date.DayOfWeek([Date])))-4)/7)+1)

Explanation:

If the first of january is on a thursday, that is week 1.

If the first of january is on a friday, that is the last week of last year.

So... The thursday is always in the right year.

If we grab a random date, and look at the thursday of that week (compensate for the weekday and add 3 to get to thursday)

We can use the function Date.DayOfYear() to determine the amount of days in the year from that point.

Then, substract 3 to get to monday, and again 1 to compensate that weekdays start at 0, but DayOfYear starts at 1. (-4 total)

This, we divide by 7 and round it.

(if thursday is in the old year, the old year is at least 4 days in that week so it should be rounded down. If thursday is in the new year, the new year is at least 4 days in that week so it should be rounded up)

This method starts 'counting' at 0, so in the end we compensate with a little +1.

[ManeAmol2022]

Nice, this helped me too, thanks for sharing.

[Anonymous]

Please look Function for ISO Year/Week number (ISO 8601)) 

NB! Pay attention to offsetindays parameter (by default it equals to zero - it means week starts on Sunday)

[o_belov]

@Mahesh0016 It works! Thanks a lot!

[GopVan · Accepted solution]

Thank You @Mahesh0016, found the solution here

[Mahesh0016]

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"


@GopVan Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!