cancel
Showing results for
Did you mean:
Highlighted
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
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

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

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors