topic Re: Function Calendar Wrong WeekNum in DAX Commands and Tips

Function Calendar Wrong WeekNum

Anonymous — Thu, 17 Sep 2020 08:03:13 GMT

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

Re: Function Calendar Wrong WeekNum

amitchandak — Thu, 17 Sep 2020 09:14:59 GMT

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

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

Re: Function Calendar Wrong WeekNum

Anonymous — Fri, 18 Sep 2020 07:21:11 GMT

Hi amitchandak

Your solution worked. Thanks a lot.

Have a nice day.

Cheers
Mike_CH

Re: Function Calendar Wrong WeekNum

Anonymous — Wed, 30 Dec 2020 14:40:39 GMT

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

Re: Function Calendar Wrong WeekNum

AntonioFemxa — Fri, 15 Jan 2021 10:57:30 GMT

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

Re: Function Calendar Wrong WeekNum

Anonymous — Tue, 19 Jan 2021 09:12:27 GMT

Thank you, Antionio! That did it 🙂

Re: Function Calendar Wrong WeekNum

PeJo — Tue, 18 May 2021 09:45:10 GMT

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

Re: Function Calendar Wrong WeekNum

Anonymous — Tue, 07 Dec 2021 14:46:09 GMT

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"))

Re: Function Calendar Wrong WeekNum

Anonymous — Fri, 21 Jan 2022 17:34:35 GMT

You absolute legend!!

Re: Function Calendar Wrong WeekNum

Anonymous — Tue, 25 Jan 2022 08:12:12 GMT

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.

Re: Function Calendar Wrong WeekNum

Anonymous — Wed, 08 Jun 2022 20:42:35 GMT

Thanks a lot!