Matching Date Ranges Across Two Tables

[Tom_G]

Hi - I have two tables - one called ShopDeliveries and one called ShopHistory - the tables are linked together on the Shop Number column. 

The answer im looking for is the Shop Owner column on the ShopDeliveries table below. To create this column I need to match the shop delivery with the correct shop owner at the time the delivery was made. 

Thanks for your help!

ShopDeliveries

Shop Number	CallingID	Arrival Date	Departure Date	Amount Delivered	Shop Owner
100345	345678	10/04/2018	11/04/2018	50	John
100346	867546	13/01/2021	16/01/2021	150	John
100347	296857	20/07/2020	26/07/2020	422	Amy
100347	981256	30/11/2020	02/12/2020	36	Justin

ShopHistory

ShopNumber	ShopOwner	Start Date	End Date	Index
100345	John	01/01/2018	15/05/2018	3
100345	Sara	15/05/2018	11/11/2019	2
100345	Lee	11/11/2019		1
100346	Tim	19/08/2018	15/09/2020	3
100346	Amy	15/09/2020	18/12/2020	2
100346	John	18/12/2020		1
100347	Bill	14/04/2018	20/06/2020	3
100347	Amy	20/06/2020	31/10/2020	2
100347	Justin	31/10/2020		1

[Jihwan_Kim]

Hi, @Tom_G 

In order to achieve the above result, I created the model like below picture.

The link to pbix file is down below.

DeliveredShop Owner =
CALCULATE (
SELECTEDVALUE ( History[ShopOwner] ),
FILTER (
VALUES ( History[Start Date] ),
History[Start Date] <= SELECTEDVALUE ( Deliveries[Arrival Date] )
),
FILTER (
VALUES ( History[End Date] ),
History[End Date] >= SELECTEDVALUE ( Deliveries[Departure Date] )
|| History[End Date] = BLANK ()
)
)

https://www.dropbox.com/s/48h3n2b8vl46bx6/TomG.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[amitchandak]

@Tom_G , Try a new column like in ShopDeliveries

maxx(filter(ShopHistory, ShopHistory[ShopNumber] = ShopDeliveries[ShopNumber] && ShopDeliveries[Departure Date]>= ShopHistory[Start Date]
&& ShopDeliveries[Departure Date] >=ShopHistory[End Date]) ,ShopHistory[ShopOwner])

[Tom_G]

Thanks  @Jihwan_Kim  - the measure produces the right result. For the sake of integration in my model I was looking for a calculated column.

I tried the DAX from @amitchandak  for the calculated column but it's not quite giving the expected result (see new column below). Any ideas how to tweak this? Thanks

ShopNumber     Arrival Date        Departure Date Shop Owner       New Column

100345  10/04/2018         11/04/2018         John     

100346  13/01/2021         16/01/2021         John      Tim

100347  20/07/2020         26/07/2020         Amy       Bill

100347  30/11/2020         02/12/2020         Justin    Justin

[Jihwan_Kim]

Hi, @Tom_G 

Can you try the below for the calculated column ?

DeliveredShop Owner column =

CALCULATE (

MAX( History[ShopOwner]),

FILTER (

VALUES ( History[Start Date] ),

History[Start Date] <= Deliveries[Arrival Date]

),

FILTER (

VALUES ( History[End Date] ),

History[End Date] >= Deliveries[Departure Date]

|| History[End Date] = BLANK ()

)

)

[Tom_G]

@Jihwan_Kim , @amitchandak - would you know how to translate Jihwan's calculated column below into the M language, so I could create the column in the Power Query Editor instead?  

Thanks a lot 

DeliveredShop Owner column =

CALCULATE (

MAX( History[ShopOwner]),

FILTER (

VALUES ( History[Start Date] ),

History[Start Date] <= Deliveries[Arrival Date]

),

FILTER (

VALUES ( History[End Date] ),

History[End Date] >= Deliveries[Departure Date]

|| History[End Date] = BLANK ()

)

)

[Tom_G]

Thanks a lot @Jihwan_Kim for your help - it works on the test file! I also tweaked the query from @amitchandak below, which also seems to work on the test file.

Unfortunately both queries are a strain on memory consumption on my full data set so BI is having issues processing them.

Column = maxx(filter(ShopHistory, ShopHistory[ShopNumber] = ShopDeliveries[ShopNumber] && ShopDeliveries[Arrival Date]>= ShopHistory[StartDate]

&& ShopDeliveries[Departure Date] <=ShopHistory[EndDate]

|| ShopHistory[ShopNumber] = ShopDeliveries[ShopNumber] && ShopDeliveries[Arrival Date]>= ShopHistory[StartDate] && ISBLANK(ShopHistory[EndDate])) ,ShopHistory[ShopOwner])