Forum Discussion
Obtain Start of week by using Year-WeekNum column from Fact Table.
Add this as a new custom column in Power Query (and maybe play around with it a little bit):
Date.StartOfWeek(Date.AddWeeks(#date(Number.FromText(Text.Start([Year-WeekNo],4)), 1, 1), Number.FromText(Text.End([Year-WeekNo],2))), Day.Monday)
However, I would recommend creating a seperate date dimension with the Year-WeekNo as a key and this new column as part of the date dimension. In this way you can also filter e.g. on week number or year.
Ah yeah, if you need all dates in a dimension instead of only the weeks (so the granularity is different now) then that's indeed the way to go 🙂
Hiii I have created Dim-Calander in data set table not in power query editor. So in my fact table I have only Year-Weekno column ad shown in screenshot. If i try to use the custom query column as you suggested,[ Date.StartOfWeek(Date.AddWeeks(#date(Number.FromText(Text.Start([Year-WeekNo],4)), 1, 1), Number.FromText(Text.End([Year-WeekNo],2))), Day.Monday)] I need date column. where will i get the date column in fact tables when I have only Year-Weekno?... I feel its going beyond 😞
Hi, you don't need the date column, you just need the Year-WeekNo to create the custom column in the fact table.
Wow Nice Ty Super User My issue is sorted.