How can I create a one to many relationship from DateKey to table with weeks?

[derickson091]

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!

DateKeyWeekly inventory table

[jdbuchanan71]

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.

[derickson091]

Is there a convenient way to do this in Power BI?

[jdbuchanan71 · Accepted solution]

Assuming a couple of items

1. Your weeks are all 7 days, starting on Monday except for the first week of the year which start on the 1st and runs to Sunday.
2. You can have more than 52 weeks in a year.

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.

[derickson091]

Awesome! Works perfectly. Thank you very much @jdbuchanan71