Need help dax measure

Hi tkavitha911 have done this using m code, please take a look. Here the daily container capacity is set to 2 for example

In the advanced editor, add this

#"Added Daysused" = Table.AddColumn(#"Changed Type", "Daysused", each Duration.Days([PredictedDelivery] - [ETA])),
#"Added Overdue Flag" = Table.AddColumn(#"Added Daysused", "Overdue flag", each [Daysused] > [FreeDays]),

DailyCounts = Table.Group(#"Added Overdue Flag", {"PredictedDelivery"}, {
{"ContainerList", each _, type table [
Plant=nullable text,
Container Number=nullable text,
ETA=nullable date,
PredictedDelivery=nullable date,
FreeDays=nullable number,
Daysused=nullable number,
Overdue flag=nullable logical
]},
{"DailyCount", each Table.RowCount(_), Int64.Type}
}),

Sorted = Table.Sort(DailyCounts, {"PredictedDelivery", Order.Ascending}),

DailyCapacity = 2,
RowCount = Table.RowCount(Sorted),

ResultList = List.Generate(
() => [i = 0, carry = 0, list = {}],
each [i] < RowCount,
each [
row = Sorted{i},
date = row[PredictedDelivery],
count = row[DailyCount],
containerList = row[ContainerList],
totalToday = count + [carry],
processed = if totalToday <= DailyCapacity then totalToday else DailyCapacity,
newCarry = if totalToday > DailyCapacity then totalToday - DailyCapacity else 0,
newRow = [
PredictedDelivery = date,
ContainerList = containerList,
DailyCount = count,
AdjustedCount = processed,
CarryForward = newCarry
],
list = [list] & {newRow},
i = [i] + 1,
carry = newCarry
]
),

CarryForwardTable = Table.FromRecords(ResultList{List.Count(ResultList)-1}[list])
in
CarryForwardTable

Hi tkavitha911,

To calculate whether the predicted delivery is delayed beyond the allowed free days, you can create a calculated column (or a measure, depending on your visual/reporting needs) using the following DAX:

Delay Beyond Free Days =
VAR DelayDays = DATEDIFF('Table'[Max of ETA], 'Table'[Predicted_Delivery_Date], DAY)
RETURN
IF(DelayDays > 'Table'[FT DAYS], DelayDays, BLANK())

For handling overflow based on daily capacity part, we’d need to simulate the logic where containers are processed based on a daily capacity limit, and any overflow is carried over to the next day.

This kind of logic is difficult to implement with DAX alone, since DAX is not well-suited for row-wise iterative logic with carryovers. Instead, I recommend using Power Query (M language) for this step.

Here’s a high-level outline of how you could implement it in Power Query:

1. Group containers by date (Predicted Delivery Date).

2. Sort dates in ascending order.

3. Define a parameter for daily capacity (e.g., 5 containers per day).

4. Use a loop or custom column logic to:

   • Check if the container count on a given date exceeds the capacity.

   • If it does, carry the excess forward and add it to the next date’s count.

   • Continue this iteratively for all dates.

This might involve writing a custom function or using a running total approach to track the overflow.

Best Regards,

Hammad.