Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 defaul...
DAX
Dax commands help
WEEKNUM
  • amitchandak's avatar
    amitchandak
    5 years ago

    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

  • PeJo's avatar
    PeJo
    5 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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