cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Aspevoll Frequent Visitor
Frequent Visitor

Get correct week number at new year, week 52, 53 or 1

Worked out a solution to calulate correct weeknumber around new year.

The week where January 1st is on a Monday, Tuesday, Wednesday og Thursday is week number 1.

The field "dato" (Norwegian) is my date field. 

I'm padding with leading a zero so I can sort in the reports.

 

If calculation of week number is 53, then if DayOfWeek is Monday(0), Tuesday(1) or Wednesday(2), the correct week number is 1, otherwise it is 53.

 

If calculation of week number is 0, then the correct week number is 1.

If not calculation of week number is 53 nor 0, it is somthing in between and the result of calculation can be used as the correct week number.

 

Week number.PNG

 

if Number.RoundDown((10+Date.DayOfYear([Dato])-Date.DayOfWeek([Dato])+1)/7)=53
then if(Date.DayOfWeek([Dato])<3)
then "01" else "53"
else if
Number.RoundDown((10+Date.DayOfYear([Dato])-Date.DayOfWeek([Dato])+1)/7)=0
then "01" else
Text.PadStart(Text.From(Number.RoundDown((10+Date.DayOfYear([Dato])-Date.DayOfWeek([Dato])+1)/7)),2,"0")

3 REPLIES 3
Microsoft v-kelly-msft
Microsoft

Re: Get correct week number at new year, week 52, 53 or 1

Hi @Aspevoll ,

 

What is the problem?What is your expected result?

 

Best Regards,
Kelly

Aspevoll Frequent Visitor
Frequent Visitor

Re: Get correct week number at new year, week 52, 53 or 1

Hi, the problem is that PowerBi calculates wrongly around new year. This list of dates are the days of week number 1, just look it up in any calendar. Anyways, the table below shows the result of PowerBi's (and Excel's) Date.WeekOfYear- or WeekNum-functions, considering weeks to start on Mondays.

30.12.2019 53
31.12.2019 53
01.01.2020 1
02.01.2020 1
03.01.2020 1
04.01.2020 1
05.01.2020 1

 

My Outlook (locale=Norway/Europe) find alle these dates to be in week 1, so does Excel's "ISOWEEKNUM", but I haven't found this function available in PowerBi - and that's why I have made this custom calculation.

 

I hope you asked in order to help getting it fixed in PowerBI, @v-kelly-msft ! 🙂

 

BR, Audun

Aspevoll Frequent Visitor
Frequent Visitor

Re: Get correct week number at new year, week 52, 53 or 1

There is also another workaround to this; if you have your data in Excel or MS SQL, prepare the week number by the functions ISOWEEK(Date) or DATEPART(ISO_WEEK, Date).

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors