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
Bratone
Helper I
Helper I

Incorrect week number 2021/2022

Hello,

 

I have a problem with the week number, in the transition from 2021 to 2022, I get this:

 

Bratone_0-1664349383992.png

 

For getting the week number I have followed this formula when adding a custom column, which I got from this topic: (https://community.powerbi.com/t5/Desktop/Incorrect-week-number-Trasnform-Date-to-Week-of-Year/m-p/57...)

 

Date.WeekOfYear([Date], Day.Sunday)

 

So as you can see I am using the same formula but for me it is not working, 1st of Jan which is a Saturday is considered as Week 1 of 2022 instead of Week 53 of 2021, even though my formula clearly specifies that the start of each week is on a Sunday, so 2nd of Jan should actually be week 1.

 

Any ideas how to fix this please ?

Thank you!

3 REPLIES 3
Syndicate_Admin
Administrator
Administrator

Over return_type,
We usually use only 1 or 2 to indicate that the week starts on Sunday or Monday, but by default the system assumes that the first week will be the one that contains January 1, but the ISO 8601 calendar standard says that the first week of the year is the one that contains 4 days or more. If you must use a "21" as a return_type , anything you put dierente to 21 will go with the first system.
Week = WEEKNUM('Calendar Table'[Date],21)
amitchandak
Super User
Super User

@Bratone , calculate week year and week number based on week start date , new columns in date table

 

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

 

Week Year =  year([Week Start Date])

 

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

 

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

Followed your formulas and I get the same thing, 1st of Jan is still WK1 (instead of WK53 of 2021) and 2nd of Jan is still WK2.

 

The only thing that I changed was in the first column, I used:

 

Week Start Date = 'Calendar'[Date]+-1*WEEKDAY('Calendar'[Date],1)+1
 
That is because I want the start of week to be on a Sunday, not on a Monday, hence the 1 instead of 2 in the weekday formula.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors