Forum Discussion
Solved
Extracting adjustment charge from one table to the other based on multiple parameters
Hello, I do have the following 2 tables that I somehow need to join: 1 - Sales Table 2 - Adjustment Charge table I need to extract the correct "Adjustment Charge" and apply it to the relev...
Hello,
I've managed to sort my issue with the following Calculated Column:
Total Cost With Adjustment = VAR CurrentLocation = Sales[SalesLocation] VAR CurrentSublocation = Sales[DeliveryLocation] VAR CurrentDate = Sales[TransactionDate] VAR CurrentQuantity = Sales[Quantity] VAR CurrentTotalCost = Sales[Total Cost] -- Find the matching adjustment charge VAR MatchingAdjustment = CALCULATE( FIRSTNONBLANK(AdjustmentTable[ChargeValue], 1), FILTER( ALL(AdjustmentTable), AdjustmentTable[SalesLocation] = CurrentLocation && AdjustmentTable[DeliveryLocation] = CurrentSublocation && CurrentDate >= AdjustmentTable[EffectiveDate] && CurrentQuantity >= AdjustmentTable[MinQuantity] && CurrentQuantity <= AdjustmentTable[MaxQuantity] ) ) RETURN IF( ISBLANK(MatchingAdjustment), CurrentTotalCost, -- No matching adjustment CurrentTotalCost + (CurrentQuantity * MatchingAdjustment) -- Apply the adjustment )
Hello,
I've managed to sort my issue with the following Calculated Column:
Total Cost With Adjustment =
VAR CurrentLocation = Sales[SalesLocation]
VAR CurrentSublocation = Sales[DeliveryLocation]
VAR CurrentDate = Sales[TransactionDate]
VAR CurrentQuantity = Sales[Quantity]
VAR CurrentTotalCost = Sales[Total Cost]
-- Find the matching adjustment charge
VAR MatchingAdjustment =
CALCULATE(
FIRSTNONBLANK(AdjustmentTable[ChargeValue], 1),
FILTER(
ALL(AdjustmentTable),
AdjustmentTable[SalesLocation] = CurrentLocation
&& AdjustmentTable[DeliveryLocation] = CurrentSublocation
&& CurrentDate >= AdjustmentTable[EffectiveDate]
&& CurrentQuantity >= AdjustmentTable[MinQuantity]
&& CurrentQuantity <= AdjustmentTable[MaxQuantity]
)
)
RETURN
IF(
ISBLANK(MatchingAdjustment),
CurrentTotalCost, -- No matching adjustment
CurrentTotalCost + (CurrentQuantity * MatchingAdjustment) -- Apply the adjustment
)