Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
tkavitha911
Helper III
Helper III

urgent help needed

‎05-30-2025 01:29 AM

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:

  1. For each record, if Day_at_port ≤ Free_days, then the Demurrage Cost = 0.
  2. If Day_at_port > Free_days, then we need to calculate the cost based on:
    • The number of days exceeding the free period.
    • The market-specific rate structure.

For example:

  • In Australia, the first 7 days are free. From the 8th day onward, each extra day is charged at a flat rate.
  • In China, the first 14 days are free. After that:
    • Days 15–18: No charge
    • Days 19–26: Charged at $96 per container per day
    • Days >26: Charged at $125 per container per day

This logic should loop through the applicable ranges and apply the correct rate based on the number of days at port.

Could you please help me write a DAX formula that handles this logic for each market?

Thank you so much for your help!



MarketsFT (Days)Flat rate>FT7-14 days 15-19 days19-26 days >26
Australia750    
China14   96125
Korea724    
shenzhen14  96125125



MarketsSupply CategoryMax of ETACount of Container NumberCapacityClearence daysFREE daysDemurrage_DaysPredicted_Delivery_DateDay_at_port
China02 MTO12-02-2025 00:00498314 16-02-2025 00:004
China01 SMR13-02-2025 00:0078314 17-02-2025 00:004
China02 MTO13-02-2025 00:0038314 17-02-2025 00:005
China01 SMR15-02-2025 00:00178314 19-02-2025 00:005
China01 SMR17-02-2025 00:0028314 20-02-2025 00:004
China01 SMR18-02-2025 00:0018314 21-02-2025 00:005
China02 MTO19-02-2025 00:0018314 22-02-2025 00:005
China01 SMR20-02-2025 00:00408314 24-02-2025 00:004
China02 MTO20-02-2025 00:0068314 24-02-2025 00:004
China01 SMR21-02-2025 00:0038314 24-02-2025 00:005
China01 SMR25-02-2025 00:0018314 28-02-2025 00:004
China01 SMR28-02-2025 00:0078314 04-03-2025 00:004
China02 MTO28-02-2025 00:0028314 04-03-2025 00:004
Australia01 SMR01-01-2025 00:001637 04-01-2025 00:004
Australia01 SMR02-01-2025 00:0064637 06-01-2025 00:005
Australia01 SMR03-01-2025 00:001637 06-01-2025 00:005
Australia01 SMR05-01-2025 00:002637 08-01-2025 00:005
Australia01 SMR09-01-2025 00:002637 12-01-2025 00:005
Australia01 SMR11-01-2025 00:0035637 15-01-2025 00:005
Australia01 SMR13-01-2025 00:003637 16-01-2025 00:005
Australia01 SMR19-01-2025 00:002637 22-01-2025 00:005
Australia01 SMR20-01-2025 00:001637 23-01-2025 00:005
Australia01 SMR22-01-2025 00:001637 25-01-2025 00:004
Australia01 SMR25-01-2025 00:006637 29-01-2025 00:004
Australia01 SMR24-02-2025 00:001637 27-02-2025 00:004
Australia01 SMR26-02-2025 00:003637 01-03-2025 00:004
Australia01 SMR27-02-2025 00:002637 02-03-2025 00:005
Australia01 SMR28-02-2025 00:001637 03-03-2025 00:004
Korea02 MTO11-02-2025 00:0051037 14-02-2025 00:006
Korea01 SMR12-02-2025 00:00101037 16-02-2025 00:006
Korea02 MTO12-02-2025 00:00161037 16-02-2025 00:006
Korea01 SMR13-02-2025 00:0031037 17-02-2025 00:006
Korea02 MTO13-02-2025 00:00101037 17-02-2025 00:006
Korea01 SMR17-02-2025 00:0031037 20-02-2025 00:006
Korea02 MTO18-02-2025 00:0011037 21-02-2025 00:005
Korea02 MTO21-02-2025 00:0051037 24-02-2025 00:005
Korea01 SMR23-02-2025 00:0051037 26-02-2025 00:006
Korea02 MTO25-02-2025 00:0051037 28-02-2025 00:006
Korea01 SMR27-02-2025 00:0011037 02-03-2025 00:006
Korea01 SMR28-02-2025 00:0031037 03-03-2025 00:006
Shenzhen01 SMR07-01-2025 00:00443  07-01-2025 00:004
Shenzhen01 SMR16-01-2025 00:00143  16-01-2025 00:005
Shenzhen01 SMR17-01-2025 00:00143  17-01-2025 00:004
Shenzhen01 SMR20-01-2025 00:00243  20-01-2025 00:005
Shenzhen01 SMR26-01-2025 00:002943 127-01-2025 00:005
Shenzhen01 SMR29-01-2025 00:00543 130-01-2025 00:004
Shenzhen01 SMR30-01-2025 00:00143  30-01-2025 00:005
Shenzhen01 SMR31-01-2025 00:00143  31-01-2025 00:004
Shenzhen01 SMR04-02-2025 00:004543 105-02-2025 00:005
Shenzhen01 SMR06-02-2025 00:00143  06-02-2025 00:004
Shenzhen01 SMR08-02-2025 00:003943 109-02-2025 00:005
Shenzhen01 SMR10-02-2025 00:001043 111-02-2025 00:004
Shenzhen01 SMR19-02-2025 00:00343  19-02-2025 00:004
Shenzhen01 SMR28-02-2025 00:001343 101-03-2025 00:005
Labels:
Message 1 of 10
740 Views
1 ACCEPTED SOLUTION
Poojara_D12
Super User
Super User

‎06-05-2025 10:51 PM

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.

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

View solution in original post

Message 10 of 10
425 Views
0
9 REPLIES 9
Poojara_D12
Super User
Super User

‎06-05-2025 10:51 PM

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.

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
Message 10 of 10
426 Views
0
techies
Super User
Super User

‎06-04-2025 02:23 AM

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
)
 
techies_0-1749029017787.png

 

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978
Message 9 of 10
476 Views
0
Anonymous
Not applicable

‎06-02-2025 05:23 AM

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

Message 5 of 10
562 Views
0
tkavitha911
Helper III
Helper III
In response to Anonymous

‎06-04-2025 01:28 AM

Thank you for your suggestions. However, the calculation isn't working correctly. It needs to function within a loop.

Message 6 of 10
491 Views
0
Anonymous
Not applicable
In response to tkavitha911

‎06-05-2025 06:06 AM

Hi @tkavitha911,


Thanks @techies for addressing the issue.

 

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

Message 7 of 10
447 Views
0
Anonymous
Not applicable
In response to Anonymous

‎06-09-2025 04:55 AM

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

Message 8 of 10
302 Views
0
maruthisp
Super User
Super User

‎06-01-2025 09:13 PM

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
)

 

Message 4 of 10
577 Views
0
SamsonTruong
Super User
Super User

‎05-30-2025 07:50 AM

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



 

Message 3 of 10
674 Views
0
bhanu_gautam
Super User
Super User

‎05-30-2025 02:48 AM

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






Message 2 of 10
704 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All