How to check if a date range contains a list of dates?

[cjohnson]

Hello! I am using Power BI to create a dashboard focusing on on-time shipment for orders.

Such that [Order Date] - [Ship Date] = [Service Delay]

Then calculating [Allowable Delay] using a few factors:

Allowable Delay =

// Route Type

IF([Route Type]="Parcel",0,1) +

//Weekday (if Order Date is Friday-> +3, Saturday +2, Sunday +1)

SWITCH(WEEKDAY([Order Date],1),1,1,2,0,3,0,4,0,5,0,6,3,7,2) +

// Certain Client/Ship Method exception (translated as +4 to allowable delay)

IF(AND([Client]="ABC",[Route Type]="Freight"),4,0) +

// If there is a Cutoff Time, was the order received after? (If so +1 to allowable delay)

IF(ISBLANK([Cutoff Time (EST)]),0,IF([Order Time (EST)] >= [Cutoff Time (EST)],1,0))

My Question

Is there a way for me to check if the range between [Order Date] and [Ship Date] contains any of the dates listed in a seperate table with Distribution Center Holidays?

ex. an Order Date of 9/2/22 with a Ship Date of 9/6/22 would generate an additional +1 since there is a holiday 9/5/22

Not proper dax syntax but trying to communicate what I mean:

IF(AND([Order Date] <= tblHoliday[Date], [Ship Date] >= tblHoliday[Date]),1,0)

                                                  ^                                               ^

                                         tblHoliday[Date] meaning "for all dates in the Date column of tblHoliday

I would like to add this as a clause to the [Allowable Delay] calculated column but I know DAX isn't loop-friendly. Thank you for any assistance! Still a relatively new developer so sorry if something here is just going over my head.

[cjohnson · Accepted solution]

Hi All, I actually was able to answer my own question once I'd posted it. I did what I sought with the below clause:

// Holiday Check

COUNTROWS(INTERSECT(tblHolidays,Calendar([DC Date], [AGI Date])))

Basically, I reduced tblHolidays to a single column of just dates in Power Query, created a pseudotable using the Calendar function to generate an inclusive list of dates between my endpoints, then counted the rows of their intersection.