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.
Hello,
I have a DateKey table shown below that I'd like to connect to a table that shows weekly inventory levels. I need to create a one-to-many relationship between the two tables, but am unable to do so (cannot join on week number because the DateKey table has multiple entries for each week).
Any ideas how to get around this?
Thank you!
Solved! Go to Solution.
Assuming a couple of items
Give this measure a try as a calculated column in your week inventory table.
Date of Week = VAR YearStart = DATE(VALUE(LEFT('Weekly Inventory'[WeekNum],4)),1,1) VAR YearStartDay = WEEKDAY(YearStart,2) VAR WeekStartDate = YearStart + VALUE(RIGHT('Weekly Inventory'[WeekNum],LEN('Weekly Inventory'[WeekNum])-4)) * 7 - 7 - (YearStartDay-1) RETURN MAX(YearStart,WeekStartDate)
Here is how the table looks in my test. Each year starts on the 1st but after that all weeks start on the Monday.
On your weekly inventory table add a column for [Week Date] which would be the date of the first day of that week. 1/1/2019 for wk1-2019, 1/8/2019 for wk2-2019 etc. The you can join the [Week Date] column to your date table.
Is there a convenient way to do this in Power BI?
Assuming a couple of items
Give this measure a try as a calculated column in your week inventory table.
Date of Week = VAR YearStart = DATE(VALUE(LEFT('Weekly Inventory'[WeekNum],4)),1,1) VAR YearStartDay = WEEKDAY(YearStart,2) VAR WeekStartDate = YearStart + VALUE(RIGHT('Weekly Inventory'[WeekNum],LEN('Weekly Inventory'[WeekNum])-4)) * 7 - 7 - (YearStartDay-1) RETURN MAX(YearStart,WeekStartDate)
Here is how the table looks in my test. Each year starts on the 1st but after that all weeks start on the Monday.
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |