Forum Discussion
LookupValue: Check DateRange for Many-to-Many
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 |
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])
1 Reply
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])