Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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.
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.
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:
Reference: https://blog.gbrueckl.at/2012/04/iso-8601-week-in-dax/
Best Regards,
Qiuyun Yu
Just read that link ... perfect this works !!
Hi @Anonymous,
I confirmed internally, the results you got is expected, the weeknum() function works fine.
Best Regards,
Qiuyun Yu
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
Hi @Anonymous,
I have consulted this issue internally, will update here once I get information.
Best Regards,
Qiuyun Yu
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |