Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mike_CH
Frequent Visitor

Function Calendar Wrong WeekNum

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_CHMismatch.JPG

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Mike_CH , 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

View solution in original post

PeJo
Advocate I
Advocate I

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

View solution in original post

10 REPLIES 10
subha17
Regular Visitor

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.

Michella
Helper I
Helper I

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"))
Michella_0-1638888188065.png

 

PeJo
Advocate I
Advocate I

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
Not applicable

Thanks a lot!

Anonymous
Not applicable

You absolute legend!!

amitchandak
Super User
Super User

@Mike_CH , 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
Not applicable

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

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
Not applicable

Thank you, Antionio! That did it 🙂 

Hi amitchandak

 

Your solution worked. Thanks a lot.

 

Have a nice day.

Cheers
Mike_CH

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors