cancel
Showing results for
Did you mean:
Helper III

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Calculating transit time excluding situational dates and holidays for origin and destination

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

Highlighted
Microsoft

## Re: Calculating transit time excluding situational dates and holidays for origin and destination

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