Calculating transit time excluding situational dates and holidays for origin and destination

[zaidmasad]

Hello,

I have a set of data for shipments, and I need to calculate the transit time but the holidays have to be excluded based on the origin and the destination.

My Shipments sheet look like this:

ID	Origin Entity	Destination Entity	Start Date	End Date
145413	AMM	CAI	2016-05-03	2016-05-05
145412	DXB	JED	2016-05-03	2016-05-6
145415	JED	JED	2016-05-01	2016-05-04
145411	AMM	JED	2016-05-03	2016-05-07
145422	DXB	JED	2016-05-01	2016-05-09
145462	DXB	JED	2016-05-04	2016-05-09
14547	JED	JED	2016-05-04	2016-05-14
14545	RUH	LON	2016-05-01	2016-05-03
14541	MCT	YYZ	2016-05-01	2016-05-04
145419	AMM	CAI	2016-05-02	2016-05-04

And I have another Calendar table and a table for the situational dates each with its entity:

situationDate	entity
5/1/2016	AMM
5/3/2016	DXB
5/8/2016	RUH
5/11/2016	MCT
5/30/2016	EBL

Please advise how can i do such thing

[v-huizhn-msft · Accepted solution]

Hi @zaidmasad,

First, you need to create a relationship between the shipment and situation tables.



Then create two calculated columns using the formulas. You will get the ransit time excluding situational date.

situation = IF(ISBLANK(RELATED(situation[situationDate])),0, CALCULATE(COUNT(situation[situationDate]),FILTER(Shipments,Shipments[Start Date]<=RELATED(situation[situationDate])&&Shipments[End Date]>=RELATED(situation[situationDate]))))

transit time = DATEDIFF(Shipments[Start Date],Shipments[End Date],DAY)-Shipments[situation]

In addition, you want to exclude the holidays, what's the holiday, could you please share more details?

Thanks,
Angelia