I used a new blank function to generate ISO 8601 WeekNumbers and the result is perfect.
Is het possible to add some code to the script (check original post at the end of this message) so it can generate week 0 en week 53 for the broken weeks where the begining of the week belongs to last year (week 53) and the rest of the week belongs to this year week (0)?
Here is an example:
day week
Sunday 30-12-2018 52
Monday 31-12-2018 53
Tuesday 1-1-2019 1
Thursday 31-12-2020 53
Friday 1-1-2021 0
Saturday 2-1-2021 0
Sunday 3-1-2021 0
Monday 4-1-2021 1
In QlikView we used this code:
if(WeekYear(TempDate)=Year(TempDate),
Week(TempDate),
if(WeekYear(TempDate)>Year(TempDate),53,0)) AS KalenderWeekJaar,
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13507155-iso-week-number-option-o...
Original post from Maurizio Loffredo:
Hi All,
After waiting for ages, I realized that "every man for himself" would have worked much better than Microsoft Engineers.
So, based on some comments (herein, thanks guys) which refer to some useful web resources, the best way to get this is by creating a lean, separate, custom function to Invoke into your calendar table when adding the ISO Week Column.
Here it is:
let
ISO8601Week = (Date as date) =>
let
AncillaryWeek = (Date as date) =>
let
WeekDay = 1 + Date.DayOfWeek(Date, Day.Monday),
OrdinalDay = Date.DayOfYear(Date),
AncillaryWeekNumber = Number.RoundDown((OrdinalDay - WeekDay + 10) / 7)
in
AncillaryWeekNumber,
ThisYear = Date.Year(Date),
PriorYear = ThisYear - 1,
AncillaryNumber = AncillaryWeek(Date),
LastWeekOfPriorYear = AncillaryWeek(#date(PriorYear, 12, 28)),
LastWeekOfThisYear = AncillaryWeek(#date(ThisYear, 12, 28)),
WeekNumber = if AncillaryNumber < 1 then LastWeekOfPriorYear else
if AncillaryNumber > LastWeekOfThisYear then 1 else AncillaryNumber
in
WeekNumber
in
ISO8601Week
