cancel
Showing results 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

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:

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
Helper V

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

4 REPLIES 4
Super User

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

``WeekNumber  = WEEKNUM([Date], 2)``

Proud to be a Super User!

Helper V

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.

Super User

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.

Proud to be a Super User!

Helper V

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

Announcements

#### 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 Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors