cancel
Showing results for
Search instead for
Did you mean:
Helper III

## Incorrect week number at year end / beginning

Hi,

I have a problem with weeks numbers in a date table where I need date 2020-12-28 to 2021-01-3 to be week 53, and 2021-01-04 to 2021-01-10 to be w 1. Week 2 shall start at 2021-01-11. As shown in below picture this is not what I get using the forumula for Weeknumber as follows: "WeekNumber", each Date.WeekOfYear(([Date]),Day.Monday)).

Does anyone have any suggestion on how to fix this?

2 ACCEPTED SOLUTIONS
Continued Contributor

You also have a solution to calculate ISO Week Number (First week of 4 days), using M code to add to your code creating your date table :

``````//Calculate ISO Week Num in 4 steps
//Step1. Calculate the date of the Thursday of the week
InsertCurrThursday = Table.AddColumn(InsertMonthEnding, "CurrThursday", each Date.AddDays([DateRef], -Date.DayOfWeek([DateRef],1) + 3), type date),
//Step2. Calculate the 1st january of the date (cf step1)
InsertFirstJan = Table.AddColumn(InsertCurrThursday, "FirstJan", each #date(Date.Year([CurrThursday]),1,1),type date),
//Step3. Calculate the number of days between the 1st january and Thursday
InsertDuration= Table.AddColumn(InsertFirstJan, "Duration", each Duration.Days(Duration.From([CurrThursday] - [FirstJan])), type number),
//Step4. Divide the number of days (duration)calculated on step3 by 7
//Roud it down and add 1
InsertISOWeekNum = Table.AddColumn(InsertDuration, "NumSemISO", each Number.RoundDown([Duration]/7)+1), ChangeType5=Table.TransformColumnTypes(InsertISOWeekNum,{{"NumSemISO", Int64.Type}}),
//Delete unusefull columns
RemovedColumns = Table.RemoveColumns(ChangeType5, {"CurrThursday","FirstJan","Duration"})``````

I'm using the rules defined by the ISO norm :

For further details :

https://en.wikipedia.org/wiki/ISO_week_date

Hope it helps

Continued Contributor

Hi,

In your first screenshot, it seems that weeks number are sorted like text, so I think that if you format them as numbers, or use 01 instead of 1 it should do the trick.

Screenshot number is more strange, do you sort it by week numbers ?

Have a nice day,

12 REPLIES 12
New Member

Hi Helena,

I used:

Week= WEEKNUM('Date',21)

It works for me.

New Member

I found in this link a much simples answer <https://blog.gbrueckl.at/2012/04/iso-8601-week-in-dax/>

Just inserting another parameter into the WEEKNUM convert function.

WEEKNUM([Date],2) --> WEEKNUM([Date],21)

Continued Contributor

It seems to be a new feature according to DAX Guide, updates Jan 2021.

For those who wants to know more about it :

https://dax.guide/weeknum/

Thanks for the information 🙂

Continued Contributor

Hi,

In your first screenshot, it seems that weeks number are sorted like text, so I think that if you format them as numbers, or use 01 instead of 1 it should do the trick.

Screenshot number is more strange, do you sort it by week numbers ?

Have a nice day,

Helper III

That solved both screenshots, thanks!

Super User II

Power Query isn't able to calculate ISO calender week. You have to do it manulally with a custom function like this:

``````// This function expects a valid date as argument
let
fxWeekOfCalendar = (Data as date) =>
let
Weekday = Date.DayOfWeek(Data) + 1,
Part1 = Number.From(Data) - Weekday + 11,
Part2 = Number.From(#date(Date.Year(Date.From(Number.From(Data) + 4 - Weekday)),1,1)),
Part3 = (Part1 - Part2) / 7,
Tranc = Part3 - Number.Mod(Part3, 1)
in
Tranc
in
fxWeekOfCalendar``````

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Continued Contributor

You also have a solution to calculate ISO Week Number (First week of 4 days), using M code to add to your code creating your date table :

``````//Calculate ISO Week Num in 4 steps
//Step1. Calculate the date of the Thursday of the week
InsertCurrThursday = Table.AddColumn(InsertMonthEnding, "CurrThursday", each Date.AddDays([DateRef], -Date.DayOfWeek([DateRef],1) + 3), type date),
//Step2. Calculate the 1st january of the date (cf step1)
InsertFirstJan = Table.AddColumn(InsertCurrThursday, "FirstJan", each #date(Date.Year([CurrThursday]),1,1),type date),
//Step3. Calculate the number of days between the 1st january and Thursday
InsertDuration= Table.AddColumn(InsertFirstJan, "Duration", each Duration.Days(Duration.From([CurrThursday] - [FirstJan])), type number),
//Step4. Divide the number of days (duration)calculated on step3 by 7
//Roud it down and add 1
InsertISOWeekNum = Table.AddColumn(InsertDuration, "NumSemISO", each Number.RoundDown([Duration]/7)+1), ChangeType5=Table.TransformColumnTypes(InsertISOWeekNum,{{"NumSemISO", Int64.Type}}),
//Delete unusefull columns
RemovedColumns = Table.RemoveColumns(ChangeType5, {"CurrThursday","FirstJan","Duration"})``````

I'm using the rules defined by the ISO norm :

For further details :

https://en.wikipedia.org/wiki/ISO_week_date

Hope it helps

Anonymous
Not applicable

For some reason, I am getting an expression error:

Expression.Error: The name 'InsertMonthEnding' wasn't recognized. Make sure it's spelled correctly.

Helper III

Hi, that worked perfect! Thanks!

Continued Contributor

Hi,

Perfect!

Do not forget to accept the solution so others can find it 😉

Gracias and good day

Helper III

Hi again @AilleryO ,

I realized I have the problems with the dates and weeks not sorting correctly using exactly the formula as you so kindly recommended, any ideas what causes this? /Helena

Super User IV

@helenawickstrom , have these columns in your date table

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

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors