cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DaveEveraers Frequent Visitor
Frequent Visitor

ISO8601 Week Numbers

 

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

2 REPLIES 2
Community Support Team
Community Support Team

Re: ISO8601 Week Numbers

@DaveEveraers ,

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Calendar-starts-with-incorrect-week-no/m-p/384319#M174963

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DaveEveraers Frequent Visitor
Frequent Visitor

Re: ISO8601 Week Numbers

How can we create a week 0 in this example?

 

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