Forum Discussion
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?
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
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,
15 Replies
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)
- AilleryO
Memorable Member
It seems to be a new feature according to DAX Guide, updates Jan 2021.
For those who wants to know more about it :
Thanks for the information 🙂
- FrankAT
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 fxWeekOfCalendarWith 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
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
- amitchandak
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 - AilleryO
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,
Please look Power Query function for ISO Year and/or Week number (ISO 8601) with optional offset and format
NB! Pay attention to offsetindays parameter (by default it equals to zero - it means week starts on Sunday)
