Forum Discussion
MH3
Helper V
Helper VGetting Date from Week Number and Year
Hi, I have a fact table in which I have Week Number and Year wise Sales I want to create a relationship of fact table with my calendar Table (Which have all dates from Year 2020 and 2021...
It's working well 🙂
MH3Helper V
Helper Vbcdobbs Well I had used that
Date.StartOfWeek(Date.AddDays(#date([Year],1,1), ([WeekNumber]-1)*7))
But now I had used a function like this
= (TheYear as number, TheWeek as number) as date =>
let
//test
//TheYear = 2018,
//TheWeek = 1,
//
offsetToISO = Date.AddDays(#date(TheYear,1,1),-4),
dayOfWeek = Date.DayOfWeek(offsetToISO, Day.Monday),
offset = -dayOfWeek + (TheWeek * 7),
isoWeekDate = Date.AddDays(offsetToISO, offset)
in
isoWeekDate
Now, my StartDateWeek shows like this
is it Correct or not?
Can I join Calendar (Date Column) with my Fact (StartDateWeek) ??
mike_honey
Memorable Member
Memorable MemberI just stumbled across this hunting for an example ISO week => Date conversion. But the code doesn't look right if these are ISO weeks - WRT wikipedia the starting point is finding out the weekday of Jan 4, but the code above examines Jan 1.
https://en.wikipedia.org/wiki/ISO_week_date#Calculating_an_ordinal_or_month_date_from_a_week_date