Forum Discussion
Obtain Start of week by using Year-WeekNum column from Fact Table.
Hi Guyz,
Need Help!!..I need to Extract Week Start Date from Fact Tables. However, I have only Year with Week No in my fact table. My I know wthat is the actual Dax or Solution to my Query. I think its little tricky. Please help 🙂
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.
6 Replies
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.Hii.. I will try as you suggested using Custom Column. Coming to Dim Date table, I have already created DIM_Calander Table in my modle. If I create Week Start Date in my fact tables. It will be v much helpful for me to build One to Many Relationship from Dim to fact table. As of now its built many to many relationship. I am facing issue While doing calculation of values compared to last year. I have connected from Year-weekno from DIm_calender to my other fact tables as Many to Many relationship. so if i can get Week Start Date I can connect from Dim_Calander (Date) to Fact(Week Start Date).
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 🙂