cancel
Showing results for
Did you mean:
Highlighted
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!

DateKeyWeekly inventory table

1 ACCEPTED SOLUTION

Accepted Solutions
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.

4 REPLIES 4
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.

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

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

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

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 6 members 1,701 guests
Recent signins: