Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
@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,
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)
Hi Helena,
I used:
Week= WEEKNUM('Date',21)
It works for me.
This is Excel function, not a power query function
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)
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 🙂
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,
That solved both screenshots, thanks!
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
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)
@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 @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
@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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |