BUG - WEEKNUM not working as expected week of new year

[Anonymous]

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. 

[Anonymous]

The same WEEKNUM error happens in Excel. There is no calendar in the world where week number 53 exists. Excel and Power BI returning week 53 is a bug that needs to be fixed.

[LimaLinks]

The bug doesn't seem to have been fixed. I am using WEEKNUM to get week numbers in 2021. This year's first week started on 4th January (ISO), a Monday. With 2020's last week, 53, ending on January 3rd 2021, 3 days into the year. Instead of returning 4th - 10th January as Week 1 it is returning Week 2 and Week 1 is simply ommitted. 

[v-qiuyu-msft]

Hi @Anonymous,

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 

[abeinke2018]

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

[Anonymous]

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.

[v-qiuyu-msft]

Hi @Anonymous,

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

Best Regards,
Qiuyun Yu 

[Anonymous]

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.

[v-micsh-msft]

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

[abeinke2018]

Hi, I agree there is a bug in this !

How can we fix this

Thanks

Andrew

[v-qiuyu-msft]

Hi @Anonymous,

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

Best Regards,
Qiuyun Yu