Need Urgent Help to fix this dax measure

[tkavitha911]

Hi Team

I need your help to calculate the PD Date using the following logic:

1. For each day:

   • If the total number of containers for that day is more than the allowed capacity, then:
     • PD Date = Max ETA + Clearance Days + 1
   • If it's within the capacity, then:
     • PD Date = Max ETA + Clearance Days

2. This check should continue daily, and also consider:

   • If the containers left from the previous day plus today’s containers are more than the capacity, then:
     • PD Date = Max ETA + Clearance Days + 1
   • Otherwise:
     • PD Date = Max ETA + Clearance Days

Could you please help me apply this logic and get the correct PD Dates?

[tkavitha911]

Markets	Supply Category	Max of ETA	Count of Container Number	Capacity	Clearence days
China	02 MTO	01-01-2025 00:00	1	8	3
China	01 SMR	06-01-2025 00:00	11	8	3
China	01 SMR	07-01-2025 00:00	12	8	3
China	02 MTO	07-01-2025 00:00	10	8	3
China	01 SMR	10-01-2025 00:00	27	8	3
China	02 MTO	10-01-2025 00:00	2	8	3
China	01 SMR	13-01-2025 00:00	1	8	3
China	01 SMR	14-01-2025 00:00	6	8	3
China	01 SMR	18-01-2025 00:00	10	8	3
Australia	01 SMR	01-01-2025 00:00	1	6	3
Australia	01 SMR	02-01-2025 00:00	64	6	3
Australia	01 SMR	03-01-2025 00:00	1	6	3
Australia	01 SMR	05-01-2025 00:00	2	6	3
Australia	01 SMR	08-02-2025 00:00	37	6	3
Australia	01 SMR	09-02-2025 00:00	1	6	3
Australia	01 SMR	10-02-2025 00:00	1	6	3
Australia	01 SMR	11-02-2025 00:00	2	6	3
Australia	01 SMR	12-02-2025 00:00	2	6	3
Australia	01 SMR	14-02-2025 00:00	7	6	3
Japan	02 MTO	23-01-2025 00:00	23	2	4
Japan	01 SMR	24-01-2025 00:00	3	2	4
Japan	01 SMR	25-01-2025 00:00	1	2	4
Japan	02 MTO	25-01-2025 00:00	4	2	4
Japan	02 MTO	26-01-2025 00:00	1	2	4
Japan	02 MTO	27-01-2025 00:00	1	2	4
Japan	01 SMR	30-01-2025 00:00	1	2	4
Japan	02 MTO	31-01-2025 00:00	1	2	4
Japan	02 MTO	02-02-2025 00:00	5	2	4
Korea	01 SMR	31-01-2025 00:00	4	10	3
Korea	02 MTO	01-02-2025 00:00	10	10	3
Korea	01 SMR	03-02-2025 00:00	5	10	3
Korea	02 MTO	03-02-2025 00:00	1	10	3
Korea	02 MTO	07-02-2025 00:00	1	10	3
Korea	02 MTO	11-02-2025 00:00	5	10	3
Korea	01 SMR	28-02-2025 00:00	3	10	3
Australia	02 MTO	06-01-2025 00:00	1	6	3

[FreemanZ · Accepted solution]

hi @tkavitha911 ,

not sure if i really get you, try to add a calculated column like:

PD Date  = 
VAR _leftover =
SUMX(
    FILTER(
        data,
        data[markets] = EARLIER(data[markets])
            &&data[max of eta] < EARLIER(data[max of eta])
    ),
    [count of container number] - [Capacity]
)
VAR _result =
IF(
    _leftover+[count of container number] > [capacity],
    [max of eta] + [clearence days]+1,
    [max of eta] + [clearence days]
)
RETURN _result

it worked like: