Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zaidmasad
Helper III
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:

IDOrigin EntityDestination EntityStart DateEnd Date
145413AMMCAI2016-05-032016-05-05
145412DXBJED2016-05-032016-05-6
145415JEDJED2016-05-012016-05-04
145411AMMJED2016-05-032016-05-07
145422DXBJED2016-05-012016-05-09
145462DXBJED2016-05-042016-05-09
14547JEDJED2016-05-042016-05-14
14545RUHLON2016-05-012016-05-03
14541MCTYYZ2016-05-012016-05-04
145419AMMCAI2016-05-022016-05-04

 

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

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

 

Please advise how can i do such thing

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @zaidmasad,

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

1.PNG

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]

2.PNG

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

Thanks,
Angelia


View solution in original post

1 REPLY 1
v-huizhn-msft
Employee
Employee

Hi @zaidmasad,

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

1.PNG

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]

2.PNG

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

Thanks,
Angelia


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.