DAX WEEKNUM gives incorrect values since jan 1st 2021

[Anonymous]

In short: I live in Europe and DAX/WEEKNUM does not give the same results als other Microsoft products like Outlook and the DAX/WEEKNUM logic is not according to the ISO 8601 calendar standard. Please change this.

Longer: 

I live in Europe.

I use this formula in a DAX calulated date table:

"YearWeek", FORMAT ( [Date], "yyyy" ) & " " & FORMAT(WEEKNUM([date],2),"00")

Result:

dax weeknum

But when I look at another Microsoft product, Outlook, this is the result.

microsoft outlook

The difference is that week 1 in DAX/WEEKNUM only has 3 days. And from after that week all numbers will be different from Outlook/the European ISO 8601 calendar standard, for the entire year.

This is documented here: https://docs.microsoft.com/en-us/dax/weeknum-function-dax

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.

So now with this behavior Power BI will differ from other Microsoft applications and also the European standard.

Why?

The only option I now see is making a custom date table but that has a lot of impact if I have to implement that in all our reports.

My suggestion is changing the DAX/WEEKNUM logic to match the logic in other Microsoft products like Outlook and thus the European standard.

[Anonymous]

Apparently the same behaviour occurs in PowerQuery

https://community.powerbi.com/t5/Issues/Transform-date-to-weeknumer-in-year-gives-the-wrong-result/i...

[v-lili6-msft]

hi  @Anonymous 

As the document said:

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.

it is designed by default for now, for any feedback and suggestions for power bi, you could submit them in here. 

Regards,

Lin

[Anonymous]

Hi mikejarod,

I'm from Europe aswell and I use a slight "hack" to get the correct week number:
Use WEEKNUM( [Date], 21). Even though it may say choose "1" or "2" by using 21 I do get the correct 53 during the first dates of this year.

To fix the WeekYear problem, you must remember by saying YEAR of the [Date] it just pick the Year of the date, ignoring the context of the week.

I work around this problem to actually get 2020-53 the first days of January 2021 by using this Code:
This is of course part of a larger DimDate calculate table, the I use

"WeekYear", VAR WeekNumber = VALUE( WEEKNUM( [Date], 21) )

VAR MonthNumber = VALUE( MONTH( [Date] ) )

VAR YearNumber = VALUE( YEAR( [Date] ) )

VAR Result = IF( LEN( WeekNumber ) = 2, WeekNumber, "0" & WeekNumber )

VAR CrossYearWeekLogic = IF( WeekNumber = 1 && MonthNumber = 12, YearNumber +1,

IF( WeekNumber = 53 && MonthNumber = 1, YearNumber - 1, YearNumber ) )

RETURN CrossYearWeekLogic & "-" & Result,

Hope it may help you!

[Anonymous]

Thanks, thats useful!

[jsanc22e]

@Anonymous great! thank you. it works