jeroen dekker on 03 Sep 2020 19:49:26
To create weeknumbers in DAX we use the DAX function WEEKNUM. The standard system it uses is the US system.
To get the correct the correct weeknumber according to ISO 8601. Which is used is Europe, parts of Asia and Australia and many other countries. We now have to choose a return type.
WEEKNUM('Calendar'[Date]',21)
The intellisense only give 2 option for the return type.
1 (week starts sunday)
2 (Monday).
How ever neither will give you the right weeknumber in Years that follow years that have 53 weeks in the ISO system. (2021 will be a year where using Type 2 will give you the wrong weeknumber every single week).
Most people solve this by using an Type 21. Which is a bit of a secret. Because it is not shown as an option by the intellisense or official documentation.
Can we get a ISOWEEKNUM() function just like in Excel. Which will be much easier for users to understand.
Especially with 2021 coming. The first year where it would actually cause a problem since the introduction of Power BI i think it is time to make this a little bit simpler.
Best regards,
Jeroen
- Comments (3)
- Merged Idea (1)
RE: ISOWEEKNUM() function DAX
To create weeknumbers in DAX we use the DAX function WEEKNUM. The standard system it uses is the US system.
To get the correct the correct weeknumber according to ISO 8601. Which is used is Europe, parts of Asia and Australia and many other countries. We now have to choose a return type.
WEEKNUM('Calendar'[Date]',21)
The intellisense only give 2 option for the return type.
1 (week starts sunday)
2 (Monday).
How ever neither will give you the right weeknumber in Years that follow years that have 53 weeks in the ISO system. (2021 will be a year where using Type 2 will give you the wrong weeknumber every single week).
Most people solve this by using an Type 21. Which is a bit of a secret. Because it is not shown as an option by the intellisense or official documentation.
Can we get a ISOWEEKNUM() function just like in Excel. Which will be much easier for users to understand.
Especially with 2021 coming. The first year where it would actually cause a problem since the introduction of Power BI i think it is time to make this a little bit simpler.
Best regards,
Jeroen
The 21 part is not an option according to the intellisense.
RE: ISOWEEKNUM() function DAX
I also need this in Power Query (M), as I use a Function Query to generate my Date tables.
RE: ISOWEEKNUM() function DAX
Working for a Global Company based out of Europe that uses this calendar, this would be GREAT addition to power BI.