Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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. 

10 REPLIES 10
Anonymous
Not applicable

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
New Member

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
Community Support
Community Support

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 

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.

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

 

Anonymous
Not applicable

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.

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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.

 

 

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

Hi, I agree there is a bug in this !

 

How can we fix this

 

Thanks

 

Andrew

 

Calendar Week Number Issue

Hi @Anonymous,

 

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.