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

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

Announcements

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors