cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
derickson091 Frequent Visitor
Frequent Visitor

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

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!

 

datekey.PNGDateKeyWeeklyInv.PNGWeekly inventory table

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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.

DateOfWeek.jpg

4 REPLIES 4
Super User
Super User

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

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 Frequent Visitor
Frequent Visitor

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

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

Super User
Super User

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

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.

DateOfWeek.jpg

derickson091 Frequent Visitor
Frequent Visitor

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

Awesome! Works perfectly. Thank you very much @jdbuchanan71 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 6 members 1,701 guests
Please welcome our newest community members: