Forum Discussion
Function Calendar Wrong WeekNum
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
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
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
Thank you, Antionio! That did it 🙂