Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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.
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 🙂
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |