Extracting adjustment charge from one table to the other based on multiple parameters

[maxf94]

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 relevant sale. In order to do that I'll need to check for multiple parameters. 

The "Adjustment Charge" table has got multiple values in it depending on the Quantity ordered:

Location  Sublocation  Min Max Charge

Location1 Sublocation2 0   100    10

Location1 Sublocation2 100 200   5

etc.

I'm looking for something along the lines of:

IF
Location1 = Location2
Sublocation1 = Sublocation2
Date1 <= Date2
Quantity1 <= Quantity2

THEN TotalCost1 + AdjustmentCharge2

I was hoping to do it via a Calculated Column, however I'm struggling to find a proper relationships for those tables as every one I can think of is a many-many relationship.

Any help would be appreciated!

[maxf94 · Accepted solution]

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
    )

[Akash_Varuna]

Hi @maxf94 ,Can't you do a merge and then add a custom column for this

• Merge Queries:

  • In Power Query, merge the "Sales Table" with the "Adjustment Charge Table" based on Location and Sublocation.
  • After merging, filter rows where:

    • SalesTable[Quantity] falls between AdjustmentChargeTable[Min] and AdjustmentChargeTable[Max].

    • SalesTable[Date] is on or before AdjustmentChargeTable[Date].

• Add Custom Column:

  [TotalCost] + [Charge]

  You could do this in Dax also something kind of like this

Adjusted_Total_Cost =
VAR CurrentLocation = SalesTable[Location]
VAR CurrentSublocation = SalesTable[Sublocation]
VAR CurrentDate = SalesTable[Date]
VAR CurrentQuantity = SalesTable[Quantity]

RETURN
    SUMX(
        FILTER(
            'AdjustmentChargeTable',
            AdjustmentChargeTable[Location] = CurrentLocation &&
            AdjustmentChargeTable[Sublocation] = CurrentSublocation &&
            AdjustmentChargeTable[Min] <= CurrentQuantity &&
            AdjustmentChargeTable[Max] >= CurrentQuantity
        ),
        AdjustmentChargeTable[Charge]
    ) + SalesTable[TotalCost]
​

If this post helped please do give a kudos and accept this as a solution 

Thanks In Advance