Forum Discussion

tkavitha911's avatar
tkavitha911
Icon for Helper III rankHelper III
1 year ago
Solved

urgent help needed

Hi Team I need your support in creating a DAX formula to calculate Demurrage Cost based on two tables, with the logic applied market-wise. Here's the requirement: For each record, if Day_at_port ≤...
urgent help for this dax
  • Poojara_D12's avatar
    Poojara_D12
    1 year ago

    Hi tkavitha911 

    Demurrage_Cost = 
SWITCH(
    TRUE(),
    'Table'[Day_at_port] <= 'Table'[FREE days], 0,

    'Table'[Markets] = "Australia", 
        ('Table'[Day_at_port] - 7) * 50 * 'Table'[Count of Container Number],

    'Table'[Markets] = "Korea", 
        ('Table'[Day_at_port] - 7) * 24 * 'Table'[Count of Container Number],

    'Table'[Markets] = "China",
        VAR ExtraDays = 'Table'[Day_at_port] - 14
        VAR Charge =
            SWITCH(
                TRUE(),
                ExtraDays <= 4, 0,
                ExtraDays <= 12, (ExtraDays - 4) * 96,
                (8 * 96) + (ExtraDays - 12) * 125
            )
        RETURN Charge * 'Table'[Count of Container Number],

    'Table'[Markets] = "Shenzhen",
        VAR ExtraDays = 'Table'[Day_at_port] - 14
        VAR Charge =
            SWITCH(
                TRUE(),
                ExtraDays <= 4, 0,
                ExtraDays <= 12, (ExtraDays - 4) * 96,
                (8 * 96) + (ExtraDays - 12) * 125
            )
        RETURN Charge * 'Table'[Count of Container Number],

    BLANK()
)

    The SWITCH(TRUE(), ...) structure allows for flexible multi-condition evaluation.

     

    For each market, we calculate ExtraDays by subtracting the free threshold from Day_at_port.

     

    Based on tiered rate structures, we apply the appropriate cost calculations and multiply by container count.

     

    This formula assumes you're applying it in a table with fields like Markets, Day_at_port, FREE days, and Count of Container Number. Adjust field names if yours differ. You can convert this into a measure with additional aggregation (e.g., SUMX) if needed at the visual level.

     

SamsonTruong's avatar
SamsonTruong
Icon for Super User rankSuper User
1 year ago

Hi tkavitha911 , depending on if you need this as a measure or a calculated column, the DAX will change slightly. I have attached a variation both both a calculated column and measure below.

DAX Calculated Column:

Demurrage Cost = 
VAR Market = Demurrage[Market]
VAR DaysAtPort = Demurrage[Day_at_port]
VAR FreeDays = Demurrage[FREE days]
VAR Containers = Demurrage[Count of Container Number]
VAR OverDays = MAX(0, DaysAtPort - FreeDays)

RETURN
SWITCH(
    TRUE(),
    Market = "Australia" && OverDays > 0, OverDays * 50 * Containers,
    Market = "Korea" && OverDays > 0, OverDays * 24 * Containers,
    Market = "China" && OverDays > 0,
        VAR Tier1 = MIN(OverDays, 4)
        VAR Tier2 = MIN(8, MAX(0, OverDays - 4))
        VAR Tier3 = MAX(0, OverDays - 12)
        RETURN (Tier2 * 96 + Tier3 * 125) * Containers,
    Market = "Shenzhen" && OverDays > 0,
        VAR Tier1 = MIN(5, OverDays)
        VAR Tier2 = MAX(0, OverDays - 5)
        RETURN (Tier1 * 96 + Tier2 * 125) * Containers,
    0
)


DAX Measure:

Total Demurrage Cost = 
SUMX(
    Demurrage,
    VAR Market = Demurrage[Markets]
    VAR DaysAtPort = Demurrage[Day_at_port]
    VAR FreeDays = Demurrage[FREE days]
    VAR Containers = Demurrage[Count of Container Number]
    VAR OverDays = MAX(0, DaysAtPort - FreeDays)

    VAR Cost =
        SWITCH(
            TRUE(),
            Market = "Australia" && OverDays > 0, OverDays * 50 * Containers,
            Market = "Korea" && OverDays > 0, OverDays * 24 * Containers,
            Market = "China" && OverDays > 0,
                VAR Tier2 = MIN(8, MAX(0, OverDays - 4))
                VAR Tier3 = MAX(0, OverDays - 12)
                RETURN (Tier2 * 96 + Tier3 * 125) * Containers,
            Market = "Shenzhen" && OverDays > 0,
                VAR Tier1 = MIN(5, OverDays)
                VAR Tier2 = MAX(0, OverDays - 5)
                RETURN (Tier1 * 96 + Tier2 * 125) * Containers,
            0
        )

    RETURN Cost
)

 

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson