Function Calendar Wrong WeekNum

[Anonymous]

Hi there,

I have a date table with the following DAX-Code: Dates = CALENDAR(DATE(2018,01,01),DATE(2030,12,31))

According to the documentation, the WeekNum function works like this:
"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."

For the year 2021 happens exactly this case. There are 1.1. - 3.1. in the week 53 of the year 2020. But the the WEEKNUM-Functions shows this three days as the week no. 1 of the year 2021. The result, the WeekNum numbers don't match with the no. in europe.

How can I fix mismatch?

Thanks for help

Cheers

Mike_CH

[amitchandak · Accepted solution]

@Anonymous , Try these column and see if last column give you desired output

Create a week start date

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday

or

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1 //Sunday

Week Year = year([Week Start date])

First Week start of year = minx(filter('Date', [Week Year] =earlier([Week Year])),[Week Start date])

Week of Year = Quotient( datediff([First Week start of year],[Date],Day),7)+1

[subha17]

hi Michelle, did you get the solution for your problem? i too face the same issue. Wk52 2021 has to show last 4 days of Dec '21 and first 3 days of Jan'22. but it shows first days of Jan'21. Pls share solution if you found one.

[Anonymous]

Hi everyone, 

I have a similar issue. My week needs to start on sunday and the column "calendarweeksunday" is sources from a snowflake database. I tried to create the yearweek which basically works but at the end of the year when it switches over to Jan 1st it will count as KW53 in year 2021 but it should be KW53 in 2020.

My current DAX is:
YEARWEEK = combinevalues("-w-", 'V_DIM_DATE (2)'[YEAR], format('V_DIM_DATE (2)'[CALENDARWEEKSUNDAY], "00"))

[PeJo · Accepted solution]

You should use WEEKNUM ( 'Date'[Date], 21 )

Returntype 21 = ISO 8601 Week or the European weeknumbering system, no need for custom formulas or functions.

You could also read more about the Weeknum function here: WEEKNUM – DAX Guide

[Anonymous]

Thanks a lot!

[Anonymous]

You absolute legend!!

[amitchandak · Accepted solution]

@Anonymous , Try these column and see if last column give you desired output

Create a week start date

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday

or

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1 //Sunday

Week Year = year([Week Start date])

First Week start of year = minx(filter('Date', [Week Year] =earlier([Week Year])),[Week Start date])

Week of Year = Quotient( datediff([First Week start of year],[Date],Day),7)+1

[Anonymous]

Hi Amitchandak, 

I tried your solution, but did not get it 100% right. My calender table starts with 2019 Oct (our fisical year goes from Oct-Sept). 

So when I used your method, first week in October becomes week 1. But it restarts as week 1 in January 2020, however not on the right day...

With your method Week 1 2020 starts on the 6th of Jan, but should be 30th of Dec 2019. 

Do you know how I could solve this? The incorrect weeks in 2019 are not important, since our reporting started  during 2020, however, I need the weeks to be correct from 2020 and forward. 

Thank you for your help!

/Vanessa

[AntonioFemxa]

Hi Vanessa,

you can substract 1 from week number if first week of year have less than 4 days.

DayOfWeek = WEEKDAY(DATE(YEAR([Date])1,1,),1)  // for calendars starting week on sunday
DayOfWeek = WEEKDAY(DATE(YEAR([Date])1,1,),2)  // for calendars starting week on monday

WeekNumber = WEEKNUM([Date]) - IF(DayOfWeek > 4, 1, 0) 

Regards.

/Antonio

[Anonymous]

Thank you, Antionio! That did it 🙂 

[Anonymous]

Hi amitchandak

Your solution worked. Thanks a lot.

Have a nice day.

Cheers
Mike_CH