topic Re: urgent help needed in DAX Commands and Tips

urgent help needed

tkavitha911 — Fri, 30 May 2025 08:29:10 GMT

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 ≤ Free_days, then the Demurrage Cost = 0.
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!

Markets	FT (Days)	Flat rate>FT	7-14 days	15-19 days	19-26 days	>26
Australia	7	50
China	14				96	125
Korea	7	24
shenzhen	14			96	125	125

Markets	Supply Category	Max of ETA	Count of Container Number	Capacity	Clearence days	FREE days	Demurrage_Days	Predicted_Delivery_Date	Day_at_port
China	02 MTO	12-02-2025 00:00	49	8	3	14		16-02-2025 00:00	4
China	01 SMR	13-02-2025 00:00	7	8	3	14		17-02-2025 00:00	4
China	02 MTO	13-02-2025 00:00	3	8	3	14		17-02-2025 00:00	5
China	01 SMR	15-02-2025 00:00	17	8	3	14		19-02-2025 00:00	5
China	01 SMR	17-02-2025 00:00	2	8	3	14		20-02-2025 00:00	4
China	01 SMR	18-02-2025 00:00	1	8	3	14		21-02-2025 00:00	5
China	02 MTO	19-02-2025 00:00	1	8	3	14		22-02-2025 00:00	5
China	01 SMR	20-02-2025 00:00	40	8	3	14		24-02-2025 00:00	4
China	02 MTO	20-02-2025 00:00	6	8	3	14		24-02-2025 00:00	4
China	01 SMR	21-02-2025 00:00	3	8	3	14		24-02-2025 00:00	5
China	01 SMR	25-02-2025 00:00	1	8	3	14		28-02-2025 00:00	4
China	01 SMR	28-02-2025 00:00	7	8	3	14		04-03-2025 00:00	4
China	02 MTO	28-02-2025 00:00	2	8	3	14		04-03-2025 00:00	4
Australia	01 SMR	01-01-2025 00:00	1	6	3	7		04-01-2025 00:00	4
Australia	01 SMR	02-01-2025 00:00	64	6	3	7		06-01-2025 00:00	5
Australia	01 SMR	03-01-2025 00:00	1	6	3	7		06-01-2025 00:00	5
Australia	01 SMR	05-01-2025 00:00	2	6	3	7		08-01-2025 00:00	5
Australia	01 SMR	09-01-2025 00:00	2	6	3	7		12-01-2025 00:00	5
Australia	01 SMR	11-01-2025 00:00	35	6	3	7		15-01-2025 00:00	5
Australia	01 SMR	13-01-2025 00:00	3	6	3	7		16-01-2025 00:00	5
Australia	01 SMR	19-01-2025 00:00	2	6	3	7		22-01-2025 00:00	5
Australia	01 SMR	20-01-2025 00:00	1	6	3	7		23-01-2025 00:00	5
Australia	01 SMR	22-01-2025 00:00	1	6	3	7		25-01-2025 00:00	4
Australia	01 SMR	25-01-2025 00:00	6	6	3	7		29-01-2025 00:00	4
Australia	01 SMR	24-02-2025 00:00	1	6	3	7		27-02-2025 00:00	4
Australia	01 SMR	26-02-2025 00:00	3	6	3	7		01-03-2025 00:00	4
Australia	01 SMR	27-02-2025 00:00	2	6	3	7		02-03-2025 00:00	5
Australia	01 SMR	28-02-2025 00:00	1	6	3	7		03-03-2025 00:00	4
Korea	02 MTO	11-02-2025 00:00	5	10	3	7		14-02-2025 00:00	6
Korea	01 SMR	12-02-2025 00:00	10	10	3	7		16-02-2025 00:00	6
Korea	02 MTO	12-02-2025 00:00	16	10	3	7		16-02-2025 00:00	6
Korea	01 SMR	13-02-2025 00:00	3	10	3	7		17-02-2025 00:00	6
Korea	02 MTO	13-02-2025 00:00	10	10	3	7		17-02-2025 00:00	6
Korea	01 SMR	17-02-2025 00:00	3	10	3	7		20-02-2025 00:00	6
Korea	02 MTO	18-02-2025 00:00	1	10	3	7		21-02-2025 00:00	5
Korea	02 MTO	21-02-2025 00:00	5	10	3	7		24-02-2025 00:00	5
Korea	01 SMR	23-02-2025 00:00	5	10	3	7		26-02-2025 00:00	6
Korea	02 MTO	25-02-2025 00:00	5	10	3	7		28-02-2025 00:00	6
Korea	01 SMR	27-02-2025 00:00	1	10	3	7		02-03-2025 00:00	6
Korea	01 SMR	28-02-2025 00:00	3	10	3	7		03-03-2025 00:00	6
Shenzhen	01 SMR	07-01-2025 00:00	4	4	3			07-01-2025 00:00	4
Shenzhen	01 SMR	16-01-2025 00:00	1	4	3			16-01-2025 00:00	5
Shenzhen	01 SMR	17-01-2025 00:00	1	4	3			17-01-2025 00:00	4
Shenzhen	01 SMR	20-01-2025 00:00	2	4	3			20-01-2025 00:00	5
Shenzhen	01 SMR	26-01-2025 00:00	29	4	3		1	27-01-2025 00:00	5
Shenzhen	01 SMR	29-01-2025 00:00	5	4	3		1	30-01-2025 00:00	4
Shenzhen	01 SMR	30-01-2025 00:00	1	4	3			30-01-2025 00:00	5
Shenzhen	01 SMR	31-01-2025 00:00	1	4	3			31-01-2025 00:00	4
Shenzhen	01 SMR	04-02-2025 00:00	45	4	3		1	05-02-2025 00:00	5
Shenzhen	01 SMR	06-02-2025 00:00	1	4	3			06-02-2025 00:00	4
Shenzhen	01 SMR	08-02-2025 00:00	39	4	3		1	09-02-2025 00:00	5
Shenzhen	01 SMR	10-02-2025 00:00	10	4	3		1	11-02-2025 00:00	4
Shenzhen	01 SMR	19-02-2025 00:00	3	4	3			19-02-2025 00:00	4
Shenzhen	01 SMR	28-02-2025 00:00	13	4	3		1	01-03-2025 00:00	5

Re: urgent help needed

bhanu_gautam — Fri, 30 May 2025 09:48:51 GMT

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)

Re: urgent help needed

SamsonTruong — Fri, 30 May 2025 14:50:20 GMT

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

Re: urgent help needed

maruthisp — Mon, 02 Jun 2025 04:13:40 GMT

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
)

Re: urgent help needed

Anonymous — Mon, 02 Jun 2025 12:23:16 GMT

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

Re: urgent help needed

tkavitha911 — Wed, 04 Jun 2025 08:28:34 GMT

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

Re: urgent help needed

techies — Wed, 04 Jun 2025 09:23:48 GMT

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,

)

Re: urgent help needed

Anonymous — Thu, 05 Jun 2025 13:06:57 GMT

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

Re: urgent help needed

Poojara_D12 — Fri, 06 Jun 2025 05:51:27 GMT

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.

Re: urgent help needed

Anonymous — Mon, 09 Jun 2025 11:55:44 GMT

Hi @tkavitha911,

Regards,
Vinay Pabbu