cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
helenawickstrom
Helper III
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?

 

 

 

Weeks.PNG

2 ACCEPTED SOLUTIONS
AilleryO
Continued Contributor
Continued Contributor

@helenawickstrom ,

 

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

View solution in original post

AilleryO
Continued Contributor
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,

 

View solution in original post

12 REPLIES 12
ecamacho
New Member

Hi Helena, 

 

I used:

 

Week= WEEKNUM('Date',21)

 

It works for me. 

 

 

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

 

vegini_0-1610622417371.png

 

AilleryO
Continued Contributor
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 🙂

AilleryO
Continued Contributor
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,

 

View solution in original post

That solved both screenshots, thanks! 

FrankAT
Super User II
Super User II

Hi @helenawickstrom 

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

04-11-_2020_12-53-19.png

 

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

AilleryO
Continued Contributor
Continued Contributor

@helenawickstrom ,

 

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

View solution in original post

Anonymous
Not applicable

Hi @AilleryO 

 

For some reason, I am getting an expression error:

 

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

 

Hi, that worked perfect! Thanks!

Hi,

Perfect!

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

Gracias and good day

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

Capture.PNGCapture7.png

amitchandak
Super User IV
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
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors