urgent help needed

tkavitha911 , Try using

dax
Demurrage Cost =
VAR Market = 'Table'[Markets]
VAR DaysAtPort = 'Table'[Day_at_port]
VAR FreeDays = 'Table'[FREE days]
VAR DemurrageDays = DaysAtPort - FreeDays
VAR Cost =
SWITCH(
TRUE(),
Market = "Australia" && DemurrageDays > 0, DemurrageDays * 50,
Market = "China" && DemurrageDays > 0 && DemurrageDays <= 4, 0,
Market = "China" && DemurrageDays > 4 && DemurrageDays <= 12, (DemurrageDays - 4) * 96,
Market = "China" && DemurrageDays > 12, (8 * 96) + ((DemurrageDays - 12) * 125),
Market = "Korea" && DemurrageDays > 0, DemurrageDays * 24,
Market = "Shenzhen" && DemurrageDays > 0 && DemurrageDays <= 4, 0,
Market = "Shenzhen" && DemurrageDays > 4, (DemurrageDays - 4) * 125,
0
)
RETURN
IF(DemurrageDays > 0, Cost, 0)

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

Hi tkavitha911 ,

I hope you have below tables: 1.Fact Table - with columns - MovementID, Market,ContainerCount,DayAtPort

2.Table with Demurrage Rates information - with columns -Market,FreeDays,FlatRate,Rate_15_18,Rate_19_26,Rate_Over26

3.Create a calculated column as below:

DemurrageCost =
VAR Market = 'ContainerMovements'[Market]
VAR DaysAtPort = 'ContainerMovements'[DayAtPort]
VAR FreeDays = RELATED('DemurrageRates'[FreeDays])
VAR ExtraDays = MAX(0, DaysAtPort - FreeDays)
VAR ContainerCount = 'ContainerMovements'[ContainerCount]

RETURN
SWITCH(
TRUE(),

// Australia: Flat rate after free days
Market = "Australia" && ExtraDays > 0,
ExtraDays * RELATED('DemurrageRates'[FlatRate]) * ContainerCount,

// Korea: Flat rate after free days
Market = "Korea" && ExtraDays > 0,
ExtraDays * RELATED('DemurrageRates'[FlatRate]) * ContainerCount,

// China: Tiered rates
Market = "China" && ExtraDays > 0,
VAR Days_19_26 = MIN(8, MAX(0, ExtraDays - 4))
VAR Days_Over26 = MAX(0, ExtraDays - 12)
RETURN (Days_19_26 * RELATED('DemurrageRates'[Rate_19_26]) + Days_Over26 * RELATED('DemurrageRates'[Rate_Over26])) * ContainerCount,

// Shenzhen: Same as China but different rates
Market = "Shenzhen" && ExtraDays > 0,
VAR Days_19_26 = MIN(8, MAX(0, ExtraDays - 4))
VAR Days_Over26 = MAX(0, ExtraDays - 12)
RETURN (Days_19_26 * RELATED('DemurrageRates'[Rate_19_26]) + Days_Over26 * RELATED('DemurrageRates'[Rate_Over26])) * ContainerCount,

// Default: No charge
0
)

Hi tkavitha911,

As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

Hi tkavitha911 please try this, this is working here

Demurrage Cost  =

VAR Market = SELECTEDVALUE('ContainerData'[Markets])

VAR DaysAtPort = SELECTEDVALUE('ContainerData'[Day_at_port])

VAR FreeDays = LOOKUPVALUE('RateStructure'[FreeDays], 'RateStructure'[Markets], Market)

VAR ExtraDays = MAX(0, DaysAtPort - FreeDays)

VAR FlatRate = LOOKUPVALUE('RateStructure'[FlatRateAfter], 'RateStructure'[Markets], Market)

VAR Rate_15_18 = LOOKUPVALUE('RateStructure'[Days_15_18], 'RateStructure'[Markets], Market)

VAR Rate_19_26 = LOOKUPVALUE('RateStructure'[Days_19_26], 'RateStructure'[Markets], Market)

VAR Rate_GT_26 = LOOKUPVALUE('RateStructure'[Days_GT_26], 'RateStructure'[Markets], Market)

RETURN

SWITCH(

    TRUE(),

   

    NOT ISBLANK(FlatRate),

        ExtraDays * FlatRate,

   

    NOT ISBLANK(Rate_19_26) || NOT ISBLANK(Rate_GT_26),

        VAR Days15to18 = MIN(4, MAX(0, ExtraDays - 0))      

        VAR Days19to26 = MIN(8, MAX(0, ExtraDays - 4))      

        VAR DaysGT26    = MAX(0, ExtraDays - 12)            

        VAR Cost19to26  = Days19to26 * COALESCE(Rate_19_26, 0)

        VAR CostGT26    = DaysGT26 * COALESCE(Rate_GT_26, 0)

        RETURN Cost19to26 + CostGT26,

   

    0

)