Forum Discussion
Getting Date from Week Number and Year
It's working well 🙂
How are you calculating the date in the fact table?
On a simple level you're getting repeated start of week dates because you have an over lap between week 53 of 2020 and week 1 of 2021. Eg it's the same week but first few days are 2020 and last few are 2021. Effectively the start of week date is actually correct because weeks don't line up perfectly with years.
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.
It's working well 🙂
I 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
