LookupValue: Check DateRange for Many-to-Many

[data_analytics]

I have two unrelated tables with M-2-M

Table A

AccountNum	Warehouse	Item Number	EFFECTIVE_DATE_KEY	EXPIRATION_DATE_KEY
006724	WH9	ItemPQR	20220101	20220930
006724	WH9	ItemPQR	20220401	20221231
006724	WH9	ItemPQR	20220501	20220930

Table B

AccountNum	Warehouse	Item Number	Quantity	DeliveryDateKey
006724	WH9	ItemPQR	4000	20221011
006724	WH9	ItemPQR	5000	20220131

AccountNum, Warehouse, and Item Number can result in multiple rows per table.

I have tried LOOKUPVALUE, NATURALLEFTOUTERJOIN (to denormalize). Results are not as expected.

How do I get Quantity from Table B if TableB.DeliveryDateKey is > TableA.EffectiveDateKey and <ExpirationDateKey? Expected result is:

AccountNum	Warehouse	Item Number	EFFECTIVE_DATE_KEY	EXPIRATION_DATE_KEY	Quantity	Notes
006724	WH9	ItemPQR	20220101	20220930	5000	20220131 bteween 20220101 and 20220930. Hence Qty=5000
006724	WH9	ItemPQR	20220401	20221231	4000	20221011 falls between 20220401 and 20221231. hence qty = 4000
006724	WH9	ItemPQR	20220501	20220930	0	Nothing found

[wdx223_Daniel · Accepted solution]

CALCULATEDCOLUMN=SUMX(FILTER(TableB,TableB[DeliveryDateKey]>TableA[EffectiveDateKey]&&TableB[DeliveryDateKey]<TableA[ExpirationDateKey]&&TableB[AccountNum]=TableA[AccountNum]&&TableB[Warehouse]=TableA[Warehouse]&&TableB[ItemNumber]=TableA[ItemNumber]),TableB[Quantity])