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 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 🙂