cancel
Showing results for
Did you mean:
Highlighted
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.

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

3 REPLIES 3
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

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

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).

Announcements

#### 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?

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

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