DAX Weeknum

[Anonymous]

Hi

Here in year 2021 I notice several of my reports where I use a "Year Week" DAX function, the result gives the wrong week number.

Data from 4th of  january to 10th should show up as in week 1, but instead it shows up in week 2. Data from 11th of january to 17th of january shows up as week 3 but should be week 2.

I'm using this coloumn formula:

Year Week = YEAR([Date]) & "-" & if(WEEKNUM([Date],2)<10,CONCATENATE(0,weeknum([Date],2)),weeknum([Date],2))

And I have tried to subtract 1 from the weeknum, if the year is 2021, but that doesn't change the result:

Year Week = YEAR([Date]) & "-" & if(YEAR([Date]) = 2021,if(WEEKNUM([Date],2)<10,CONCATENATE(0,weeknum([Date],2)-1),weeknum([Date],2)-1),if(WEEKNUM([Date],2)<10,CONCATENATE(0,weeknum([Date],2)),weeknum([Date]+1,2)))

Could any one help me on this?

[Payeras_BI]

Hi @Anonymous ,

I am glad it helped.

In additon to get the first three days of 2021 in your Year Week calculated column like 2020-53 and not 2021-53 use the following code for the ISO Year:

Regards,

[Geradav]

@Anonymous To obtain ISO Week Number you need to use 21 as the second argument as mentioned by @Payeras_BI 

[Payeras_BI · Accepted solution]

Hi @Anonymous ,

Regards,

[Anonymous]

Edit: It did actually work! 😉

Thanks, it doesn't work for me.

[amitchandak]

@Anonymous , I think this same as issue reported here

https://community.powerbi.com/t5/Issues/DAX-WEEKNUM-gives-incorrect-values-since-jan-1st-2021/idi-p/1583824

In calendar add these columns

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1

min week start of year = minx(filter('Date',year([Week Start date])=earlier(year(Week Start date]))),[Week Start date])

week No = quotient(datediff([min week start of year],[date],day),7)+1

[Anonymous]

Thanks, I will see if I can implement this in my report