Calender with weeknumbers according to ISO8601

[jwi1]

Hi all,

Below is a calender, which I use in my report.

However, the weeknumber is not according to ISO8601.

I know there is a possibility with the formula WEEKNUM: WEEKNUM([Date], 21)

the 21 gives me the weeknumber according to ISO8601.

But how must I change the tellow marked line below to get this done?

Hope someone can help me out with this!

John

[jwi1]

Dear Matt,

Highly appreciated, followed your advise, all fine now.

Have a good day!

John

[OwenAuger]

I notice @MattAllington just replied as well while I was investigating this.

After lookup up the ISO 8601 definition, I see that an ISO week runs from Monday to Sunday, and the year a given week falls in is the year containing the Thursday of that week.

Here is a formula that should work in Power Query in the context of the code you posted:

= Table.AddColumn(#"Inserted Month", "Week of Year",
  each let
    DayOfWeek = Date.DayOfWeek([Date],Day.Monday),
    ThursdayDate = Date.AddDays([Date],3-DayOfWeek),
    ThursdayDayOfYear = Date.DayOfYear(ThursdayDate)
  in
    Number.IntegerDivide(6+ThursdayDayOfYear, 7 ),
  Int64.Type
)

It basically says:

1. For the current date, find the date of the Thursday within the same week (ThursdayDate).
2. Get the day-of-year for that Thursday (ThursdayDayOfYear).
3. Then do integer division of ( ThursdayDayOfYear + 6 ) by 7.

This works because, for example, if the Thursday of a given week falls within days 1 to 7 of the year, the division in step 3 ranges between 7 / 7 to 13 / 7, all of which return 1, which is correct since that must be the first Thursday of the year. Subsequent weeks will increment accordingly.

Regards,

Owen

[MattAllington · Accepted solution]

I will take you on your word that weeknum (21) gives what you need. Weeknum is a DAX function, not a PQ function. So don't add the weeknum in PQ, but instead add it as a calculated column in DAX. I would normally not advise this, but based on your post, it would seem that it could work.