Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
deb_power123
Helper V
Helper V

Week Number calculation DAX query returning wrong results

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:

 

Error.JPG

 

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

1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4
DataInsights
Super User
Super User

@deb_power123,

 

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

 

WeekNumber  = WEEKNUM([Date], 2)

 

https://dax.guide/weeknum/ 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

 

 deb_power123_0-1621608274942.png

 

@deb_power123,

 

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

 

DataInsights_0-1621609598004.png

 

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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.