Forum Discussion
Solved
Getting 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 🙂
bcdobbs 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) ??
I think both "could" be correct. Depends on how weeks were originally defined in your source data. Personally suspect your later suggestion is better.
As far as a join to a date table yes, it'll work fine.