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

BUG - WEEKNUM not working as expected week of new year

According to your documentation.  The default WEEKNUM function should return the Week Number for any date that is in the same Sunday - Saturday week that contains January 1st. 

 

So for example, this year December 30, 2018 to January 5, 2019 should all return for the WEEKNUM function, a value of '1'.  

 

However, this is not occurring.  If you use the following formula: WEEKNUM(DATE(2018,12,31) it will return the value of '53'.  Conversely, if you use the following formula: WEEKNUM(DATE(2019,1,1) it will return the value of '1'. 

 

Your DAX formula for WEEKNUM is currently broken.  If I am mistaken and the function is correct, then please update your documentation to make this more clear. 

8 REPLIES 8
Moderator v-qiuyu-msft
Moderator

Re: BUG - WEEKNUM not working as expected week of new year

Hi @HellishFarva,

 

According the documentation

 

"By default, the WEEKNUM function uses a calendar convention in which the week containing January 1 is considered to be the first week of the year.

 

However, the ISO 8601 calendar standard, widely used in Europe, defines the first week as the one with the majority of days (four or more) falling in the new year.

 

This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are different from the ISO 8601 definition."

 

Based on ISO 8601, the 12/31/2018 is in week 1 of 2019. But WEEKNUM function deoesn't use this standard, it returns 53, this is an expected result. 

 

To make the 12/31/2018 returns weeknum as 1, you can create a measure below: 

 

measure= WEEKNUM(DATE(2018,12,31),21)

 

Reference: https://blog.gbrueckl.at/2012/04/iso-8601-week-in-dax/

 

Best Regards,
Qiuyun Yu 

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

Re: BUG - WEEKNUM not working as expected week of new year

You are exactly right. However, for this year. Regardless if you use the standard format or the ISO 8601 then this entire week would be week 1 as this week has 5 or 6 days of the new year in it depending on if you set the week to start on Sunday or Monday.

My point is that there is a bug in the system that is not reporting the correct week number if you input 12/31/2018 into the WEEKNUM function.
Moderator v-qiuyu-msft
Moderator

Re: BUG - WEEKNUM not working as expected week of new year

Hi @HellishFarva,

 

I have consulted this issue internally, will update here once I get information. 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Moderator v-qiuyu-msft
Moderator

Re: BUG - WEEKNUM not working as expected week of new year

Hi @HellishFarva,

 

I confirmed internally, the results you got is expected, the weeknum() function works fine. 

 

Best Regards,
Qiuyun Yu 

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

Re: BUG - WEEKNUM not working as expected week of new year

Hi, I agree there is a bug in this !

 

How can we fix this

 

Thanks

 

Andrew

 

Calendar Week Number Issue

abeinke2018 Frequent Visitor
Frequent Visitor

Re: BUG - WEEKNUM not working as expected week of new year

Just read that link ... perfect this works !!

 

HellishFarva Frequent Visitor
Frequent Visitor

Re: BUG - WEEKNUM not working as expected week of new year

If it is working correctly, then please update your documentation.  Here's what I mean. 

 

As previously discussed. According to https://docs.microsoft.com/en-us/dax/weeknum-function-dax

 

It states "By default, the WEEKNUM function uses a calendar convention in which the week containing January 1 is considered to be the first week of the year.  However, the ISO 8601 calendar standard, widely used in Europe, defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are different from the ISO 8601 definition."

 

If the documentation was correct, then if I put in the following into PowerBI then it should return an integer of 1

 

WEEKNUM(DATE(2018,12,30), 1)

 

However, since you are saying that the return value for the above formula is 53 and that is the correct functionality.  Then that would mean the documentation quoted above is wrong because the week (Sunday to Saturday) 12/30/2018 to 01/05/2019 should be week 1.  

 

If I am misunderstanding the documentation explanation of WEEKNUM, then please explain it and/or update the documentation in a way that can be understood as the way it is written does not produce the result expected.

 

 

Highlighted
v-micsh-msft New Contributor
New Contributor

Re: BUG - WEEKNUM not working as expected week of new year

Hi all,

 

Just quote again:

"

"By default, the WEEKNUM function uses a calendar convention in which the week containing January 1 is considered to be the first week of the year.  However, the ISO 8601 calendar standard, widely used in Europe, defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are different from the ISO 8601 definition."

 

"

The first part is how the WEEKNUM defines the first week of the Year.

So when using WEEKNUM(DATE(2018,12,30), 1), it is calculating the week numbers for the Year 2018.

 

I don't think this function will calculating the Week number for the year 2019 with a 2018 Date string.

 

The quoted part of the WEEKNUM function told that the ISO 8601 Definition for the first week is different from the WEEKNUM calculation, which has been clearly explained:

"

This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are different from the ISO 8601 definition.

"

Does anyone have any further misunderstanding on this part?

 

Regards,

Michael