Need help dax measure

[tkavitha911]

I have columns such as Plant, Container Number, Predicted Delivery Date, Maximum ETA Date, and Free Days. I need a DAX measure in Power BI that, for each market, calculates the difference between the Predicted Delivery Date and the Maximum ETA Date. If this difference exceeds the Free Days, it should return the number of days; otherwise, it should return a blank.

PLANT	Container Number	Max of ETA	Predicted_Delivery_Date	FT DAYS
Australia	TGBU51141	15-07-2025	18-07-2025	6
Australia	#AFH40	27-06-2025	30-06-2025	6
Australia	PIDU4269	11-07-2025	14-07-2025	6
Australia	PIDU4269	11-07-2025	14-07-2025	6
China	TWCU819	15-04-2025	19-04-2025	7
China	TEMU800	07-01-2025	11-01-2025	7
China	SELU4275	29-12-2024	02-01-2025	7
Japan	CCLU5181	24-01-2025	29-01-2025	4
Japan	OOCU4931	07-03-2025	12-03-2025	4
Japan	OOCU4931	07-03-2025	12-03-2025	4
Japan	CCLU529	23-05-2025	28-05-2025	4
Japan	FSCU506	23-05-2025	28-05-2025	4
Philippines	SEKU652	17-05-2025	22-05-2025	10
Philippines	CSNU401	26-05-2025	31-05-2025	10
Philippines	CBHU6474	17-03-2025	22-03-2025	10
Thailand	OOLU4416	11-01-2025	15-01-2025	10
Thailand	OOCU489	17-05-2025	21-05-2025	10
Thailand	DFSU135	16-04-2025	19-04-2025	10
Vietnam	OOCU065	07-04-2025	11-04-2025	5
Vietnam	CSLU190	19-05-2025	23-05-2025	5

[techies · Accepted solution]

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

[techies · Accepted solution]

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

[tkavitha911]

If the number of containers exceeds the daily capacity, the excess should be carried over to the next day and added to that day's container count, continuing in this manner.

[v-mdharahman · Accepted solution]

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.

[v-mdharahman]

Hi @tkavitha911,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-mdharahman]

Hi @tkavitha911,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-mdharahman]

Hi @tkavitha911,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want a DAX to calculate the difference between the Predicted Delivery Date and the Maximum ETA Date and based on certain conditions(Exceeding free days) it should return the number of days or else, it should return a blank. As @Poojara_D12, @FreemanZ, @maruthisp and @bhanu_gautam all responded to your query, please go through the solution provided by them and check if it solves your query.

Also mark the helpful reply as solution so that other community members can find the solution easily.

I would also take a moment to thank @Poojara_D12, @FreemanZ, @maruthisp and @bhanu_gautam for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @tkavitha911,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[Poojara_D12]

Hi @tkavitha911 

Exceeded Days = 
VAR DaysDiff = DATEDIFF(
    MAX('Table'[Max of ETA]), 
    MAX('Table'[Predicted_Delivery_Date]), 
    DAY
)
VAR FreeDays = MAX('Table'[FT DAYS])
RETURN
IF(DaysDiff > FreeDays, DaysDiff - FreeDays, BLANK())

• Add this measure to a table visual along with your Plant (market) and other columns.

• It will calculate and show the excess days only where the difference exceeds the free days threshold.

If you want this measure aggregated at a market level, you may need to adjust the DAX to calculate the sum or average of these excess days accordingly, depending on your needs.

This approach provides a flexible and dynamic way to identify where deliveries exceed allowed free days across different markets in your Power BI report.

[FreemanZ]

hi @tkavitha911 ,

not sure if i fully get you. It seems you expect a calculated column. Try to add such like below:

Column = 
VAR _days = INT([Predicted_Delivery_Date] - [Max of ETA])
VAR _result = IF(_days > [FT DAYS], _days)
RETURN _result

it worked like:

[maruthisp]

Hi tkavitha911,

Please find below DAX as per you requirements.
DaysOverFreeDays =
VAR _Diff =
DATEDIFF(
'Table'[Max of ETA],
'Table'[Predicted_Delivery_Date],
DAY
)
RETURN
IF(
_Diff > 'Table'[FT DAYS],
_Diff,
BLANK()
)

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[tkavitha911]

sorry, not getting values 

[bhanu_gautam]

@tkavitha911 , Try using

Days_Exceeding_Free_Days =
VAR Difference = DATEDIFF('Table'[Max of ETA], 'Table'[Predicted_Delivery_Date], DAY)
RETURN
IF(Difference > 'Table'[FT DAYS], Difference, BLANK())