Handling weekends and Consecutive Holidays in Estimated Delivery Date Calculation in DAX (Excel)

[shafiz_p]

I have order , CS Approved Date, and lead time to process the order. My job is to findout the Estimated delivery date excluding weekends and holidays. This is the formula I am using to count holidays and weekends between 2 dates :

'Count of Weekends & Holidays' =
CALCULATE(

COUNTROWS('Dates Table'),

FILTER(

'Dates Table',

'Dates Table'[Date] >= 'OrderDetails'[CS Approved Date] &&

'Dates Table'[Date] <= 'OrderDetails'[CS Approved Date] + 'OrderDetails'[Lead Time] &&

'Dates Table'[Is Working Day] = "False"

)

)

True = Working Days & False = Not Working Days (My Date table correctly Identified weekends and holidays in power query)

Problem is the estimated delivery date is again a holiday or weekends. So, formula not counting that one. In dax there is no looping capabilities in my knowledge to check the estimated delivery date is working days or not.

I have 3 calculated columns : 'Count of Weekends & Holidays', 'Total Lead Time' and 'Estimated Delivery Date'

Total Lead Time  = 'Count of Weekends & Holidays' + 'Lead Time'
Estimated Delivery Date =

IF(

NOT(ISBLANK('OrderDetails'[CS Approved Date])),

'OrderDetails'[CS Approved Date] + 'OrderDetails'[Total Lead Time]

)

For example, Order # OE200023, CS Approved Date 4/25/2024, lead time is 5 days, and Estimated delivery date is 4/30/2024. So, between 4/25/2024 and 4/30/2024, there is only one weekends, which is Friday in my case. Now the Estimated delivery date is 5/1/2024 which is a holiday (This is the problem, lead time not covering this). Formula don't know the estimated delivery date is working days or not. I can do this using the formula only once to count weekends & holidays. What about consecutive holidays, which not covered by the lead time. Specially in festival time. Main problem is, My Lead time will not cover all weekends and holidays all the time. This lead time vary product category wise and continue the same whole year until a changes come(It is system default).

I have found a solution to this problem but it can check just once that the estimated delivery date is working days or not. If , then it will return the same, if not, then it will add one more days. But problem still exist (Consecutive weekends and holidays, which not covered by the lead time or first time estimated delivery date).

Estimated Delivery Date(Fixed) =

VAR EstimatedDeliveryDate =
IF(

NOT(ISBLANK('OrderDetails'[CS Approved Date])),

'OrderDetails'[CS Approved Date] + 'OrderDetails'[Total Lead Time]

)

VAR IsHoliday = LOOKUPVALUE('Dates Table'[Is Working Day], 'Dates Table'[Date], INT(EstimatedDeliveryDate)) = "False"

RETURN IF(IsHoliday, EstimatedDeliveryDate + 1, EstimatedDeliveryDate)

How to solve this problem. I’d be grateful if you could assist me in resolving this issue.

Just to remember, I would not be able to create any new calculated column in power pivot(Dax) or in power query because of self referencing Table. Whatever formula it is , it should go in to  this 3 column.

[shafiz_p]

This is my solution:
Initial Count is

Count of Weekends & Holidays =

CALCULATE(

COUNTROWS('Dates Table'),

FILTER(

'Dates Table',

'Dates Table'[Date] >= 'OrderDetails'[CAD (Duplicate)] &&

'Dates Table'[Date] <= 'OrderDetails'[CAD (Duplicate)] + 'OrderDetails'[Lead Time] &&

'Dates Table'[Is Working Day] = "False"

)

)

The key change in the solution is the addition of a check for 5 consecutive days. The formula now stops calculating as soon as it encounters a working days within this period. It then returns the initial estimated delivery date, adjusted by adding the number of days before the working day was found. This ensures that the delivery date always falls on a working day.

Total Lead Time =

VAR InitialCount = 'OrderDetails'[Weekly Holiday]

VAR NewDate1 = 'OrderDetails'[CS Approved Date] + 'OrderDetails'[Lead Time] + InitialCount

VAR IsHoliday1 = CALCULATE(COUNTROWS('Dates Table'), FILTER('Dates Table', 'Dates Table'[Date] = NewDate1 && 'Dates Table'[Is Working Day] = "False"))

VAR NewDate2 = IF(IsHoliday1 > 0, NewDate1 + 1, NewDate1)

VAR IsHoliday2 = IF(IsHoliday1 > 0, CALCULATE(COUNTROWS('Dates Table'), FILTER('Dates Table', 'Dates Table'[Date] = NewDate2 && 'Dates Table'[Is Working Day] = "False")), 0)

VAR NewDate3 = IF(IsHoliday2 > 0, NewDate2 + 1, NewDate2)

VAR IsHoliday3 = IF(IsHoliday2 > 0, CALCULATE(COUNTROWS('Dates Table'), FILTER('Dates Table', 'Dates Table'[Date] = NewDate3 && 'Dates Table'[Is Working Day] = "False")), 0)

VAR NewDate4 = IF(IsHoliday3 > 0, NewDate3 + 1, NewDate3)

VAR IsHoliday4 = IF(IsHoliday3 > 0, CALCULATE(COUNTROWS('Dates Table'), FILTER('Dates Table', 'Dates Table'[Date] = NewDate4 && 'Dates Table'[Is Working Day] = "False")), 0)

VAR NewDate5 = IF(IsHoliday4 > 0, NewDate4 + 1, NewDate4)

VAR IsHoliday5 = IF(IsHoliday4 > 0, CALCULATE(COUNTROWS('Dates Table'), FILTER('Dates Table', 'Dates Table'[Date] = NewDate5 && 'Dates Table'[Is Working Day] = "False")), 0)

RETURN InitialCount + OrderDetails[Lead Time] + IF(IsHoliday1 > 0, 1, 0) + IF(IsHoliday2 > 0, 1, 0) + IF(IsHoliday3 > 0, 1, 0) + IF(IsHoliday4 > 0, 1, 0) + IF(IsHoliday5 > 0, 1, 0)

Finaly,

Estimated Delivery Date =

IF(

NOT(ISBLANK('OrderDetails'[CS Approved Date])),

'OrderDetails'[CS Approved Date] + 'OrderDetails'[TLT]

)

[some_bih]

Hi @shafiz_p great. Let me know if I could assist. Best!

[some_bih]

Hi @shafiz_p check example

[shafiz_p]

I am sorry but this is not my solution. I have already applied this.
I have to calculate Estimated Delivery Date. I only know Approved Date and product lead time. From this information we easily can find out when the Estimated Delivery date is. Then we can find out weekends and holidays between this 2 dates (Approved Date, and Approved Date + Lead Time). Let's say 1 weekend found. Finally estimated delivery date increased by 1 day. Now the problem is, this final estimated delivery date is a holiday or next day can be holiday, then next , then next. So, I want to make sure when ever it is, it should be working days. I hope you understand!

[some_bih]

Hi @shafiz_p share your file with sample input and expected output.

[shafiz_p]

Red Tick Mark are my input. 3rd column calculated by the formula in the post. Check it out. 4th column is the sum of 2nd and 3rd. 5th is the output. Few of the outputs are ok and few are not because of the problem I described. I want this Estimated delivery date should be working days. If working days, then formula will return this date, if not it will check next date and continues until a working days found. Just remember, I would not be able to create any new column. Columns are fixed. 

Currently the below formala of EDD, checking only one day that it is working days or not. 

Estimated Delivery Date(EDD) =

VAR EstimatedDeliveryDate =
IF(

NOT(ISBLANK('OrderDetails'[CS Approved Date])),

'OrderDetails'[CS Approved Date] + 'OrderDetails'[Total Lead Time]

)

VAR IsHoliday = LOOKUPVALUE('Dates Table'[Is Working Day], 'Dates Table'[Date], INT(EstimatedDeliveryDate)) = "False"

RETURN IF(IsHoliday, EstimatedDeliveryDate + 1, EstimatedDeliveryDate)

This is the date table. related table

You can create dummy tables. 

[some_bih]

Hi @shafiz_p your table, OrderDetails have duplicate rows? 

It should be some product id or something else unique per row in that or other tables?

[shafiz_p]

I’ve just given you the part related to the date. Indeed, there is a unique order ID. I’m on the verge of resolving this issue. Hopefully, I’ll be able to share the solution shortly. Thank you very much for working on this solution.