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

[Aspevoll]

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
Text.PadStart(Text.From(Number.RoundDown((10+Date.DayOfYear([Dato])-Date.DayOfWeek([Dato])+1)/7)),2,"0")

[Anonymous]

Just as a follow up on this, if you are trying to get ISO week No in DAX, then use WEEKNUM([Date],21). 21 is the important argument for generating the ISO week.

If you are trying to generate ISO week in Power Query, then here are some resources

//Source: Curbal https://community.powerbi.com/t5/Community-Blog/The-quot-only-quot-Power-BI-calendar-you-will-ever-n...
//Created Custom function for retrieving ISO Week Number https://www.youtube.com/watch?v=pUVu64cpcDc

[v-kelly-msft]

Hi @Aspevoll ,

What is the problem?What is your expected result?

Best Regards,
Kelly

[Aspevoll]

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]

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