Forum Discussion

Frequent Visitor

3 years ago

Solved

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

Many to Many Relatioinship

wdx223_Daniel
3 years ago
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])

Icon for Community Champion rank

Community Champion

3 years ago

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])