topic Week Number calculation DAX query returning wrong results in DAX Commands and Tips

Week Number calculation DAX query returning wrong results

deb_power123 — Fri, 21 May 2021 13:21:48 GMT

Hi All,

I have written a WeekNumber calculated column but unfortunately it is not fetching the correct week numbers. It is giving wrong results. Here is my DAX:-

WeekNumber = WEEKNUM([Date])

As per the requirement a week should start from Monday till Sunday so in that way at the moment we are in WeekNumber20 but the above DAX is fetching me incorrect results , it is calculating we are in WeekNumber 21.

Expected Output :

Is there a way we can fix this above DAX as my requirement is to show week starting from Monday till Sunday but seems the above calculation is calculating the week from Sunday till Saturday

How can we modify the above DAX to calculate the week number correctly considering week start as Monday and End as Sunday.

My current results:

Input data :-

date	location	attendance%
01.12.2020	B1	20
02.12.2020	B2	21
03.12.2020	B3	22
04.12.2020	B4	23
01.05.2021	B1	20
02.05.2021	B2	30
03.05.2021	B3	40
04.05.2021	B4	25
05.05.2021	B5	21
06.05.2021	B1	23
07.05.2021	B2	70
08.05.2021	B3	19
09.05.2021	B4	22
10.05.2021	B5	25
11.05.2021	B1	21
12.05.2021	B2	23
13.05.2021	B3	17
14.05.2021	B4	19
15.05.2021	B5	22
16.05.2021	B1	30
17.05.2021	B2	40
18.05.2021	B3	25
19.05.2021	B4	21
20.05.2021	B5	23
21.05.2021	B1	70
22.05.2021	B2	19
23.05.2021	B3	22

Regards

Sameer

Re: Week Number calculation DAX query returning wrong results

DataInsights — Fri, 21 May 2021 13:37:24 GMT

deb_power123,

Use the second argument to control the starting day of the week:

WeekNumber = WEEKNUM([Date], 2)

https://dax.guide/weeknum/

Re: Week Number calculation DAX query returning wrong results

deb_power123 — Fri, 21 May 2021 14:51:34 GMT

Hi DataInsights

Interesting information, i tried to add the parameter 2 but it still returned the same old results.

The week number is 21 instead of 20 from 17 May to 23 May 2021.

The week 1 of the year 2021 starts from 4th Jan to 10th Jan since 28 December 2020 to 3rd Jan 2021 is week number 52(or last week of the year 2020) of the year 2020.

so the first week of 2021 should start from 4th Jan which is a Monday. Could you please suggest where am I missing. If possible could you please use the source input i have provided in the above thread , you will also get the same error I am getting.

This week 17 May - 23 May is week number 20 but it shows week number 21.

Re: Week Number calculation DAX query returning wrong results

DataInsights — Fri, 21 May 2021 15:10:50 GMT

deb_power123,

It's a difference in how the first week of the year is handled. See the screenshot below:

The first week of January starts on Friday, so week 1 is a partial week. It sounds as if you want to define week 1 as the first week that contains a Monday. If so, you would have to create additional logic to handle this scenario.

https://www.timeanddate.com/date/weeknumber.html

Re: Week Number calculation DAX query returning wrong results

deb_power123 — Sat, 22 May 2021 04:28:08 GMT

I solved the issue but in different way.I am writing the solution so that anyone else can refer in the coming days .Instead of 2 use 21 as parameter.

WeekNumber = WEEKNUM([Date],21)

This will fetch correct results