Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Memorable Member
Memorable Member

@Anonymous ,

 

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
Memorable Member
Memorable Member

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

15 REPLIES 15
DenisSipchenko
Advocate III
Advocate III

Please look Function for ISO Year/Week number (ISO 8601)) 


NB! Pay attention to offsetindays parameter (by default it equals to zero - it means week starts on Sunday)

ecamacho
New Member

Hi Helena, 

 

I used:

 

Week= WEEKNUM('Date',21)

 

It works for me. 

 

 

Anonymous
Not applicable

This is Excel function, not a power query function

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

 

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
Memorable Member
Memorable Member

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,

 

Anonymous
Not applicable

That solved both screenshots, thanks! 

FrankAT
Community Champion
Community Champion

Hi @Anonymous 

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)

As long as there is no direct Microsoft-feature for ISO-weeks,

I would suggest the following solution which I guess should be the most efficient workaround:

= Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1 Wochentag", "Woche", each Date.WeekOfYear(Date.AddDays([Datum],-7)), Int64.Type)

AilleryO
Memorable Member
Memorable Member

@Anonymous ,

 

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

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.

 

Anonymous
Not applicable

Hi, that worked perfect! Thanks!

Hi,

Perfect!

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

Gracias and good day

Anonymous
Not applicable

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
Super User

@Anonymous , 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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.