Obtain Start of week by using Year-WeekNum column from Fact Table.

[Anonymous]

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 🙂

[Paulien_ · Accepted solution]

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.

[Anonymous]

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).

[Paulien_]

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 🙂 

[Anonymous]

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 😞

[Paulien_]

Hi, you don't need the date column, you just need the Year-WeekNo to create the custom column in the fact table.

[Anonymous]

Wow Nice Ty Super User My issue is sorted.