Need help with calculating correct start and end from columns to determine duration

[BPS]

Hello all,

I am looking for some assistance with finding the best way to approach the following situation:

I have two tables:

Table A - contains the ON and OFF date/times of equipment

Table B - contains the date/times of the phases of an aircraft turn

             - ON to IN is the Taxi In duration 

             - IN to OUT is the Turn duration

             - OUT to OFF is the Taxi Out duration

The only common column is the Tail #.  The values within the Tail # should relate to eachother but the number of times each tail appears in each table will vary greatly.  I need to find how many minutes was the equipment in Table A is running during the phases of the aircraft turns.  So the resulting table that I hope to acheive will show the flight information and the minutes for each of the phases.

I will share some sample data for each table along with the resulting table:

Table A:

Tail	ON	OFF
123	5/1/20 8:06 AM	5/1/20 8:31 AM
123	5/1/20 2:46 PM	5/1/20 3:55 PM
234	5/1/20 4:52 PM	5/1/20 5:29 PM
123	5/1/20 12:00 PM	5/1/20 12:23 PM
123	5/1/20 5:58 PM	5/1/20 10:32 PM
123	5/1/20 9:33 AM	5/1/20 10:01 AM
234	5/1/20 6:02 PM	5/1/20 8:32 PM
234`	5/2/20 8:20 AM	5/2/20 8:42 AM
123	5/2/20 12:00 PM	5/2/20 1:00 PM

Table B:

Flight Date	Flight #	Tail #	Origin	Destination	ON	IN	OUT	OFF
5/1/2020	111	123	ABC	BCD		5/1/20 07:47		05/01/20 08:15
5/1/2020	112	123	BCD	EFG	5/1/20 14:58	5/1/20 15:04	05/01/20 17:54	05/01/20 18:04
5/1/2020	113	234	CDE	DEF	5/1/20 16:06	5/1/20 16:16	05/01/20 17:25	05/01/20 17:35
5/1/2020	114	234	DEF	ZZY	5/1/20 18:21	5/1/20 18:31	05/02/20 08:25	05/02/20 08:40
5/1/2020	115	123	EFG	ABC	5/1/20 18:36	5/1/20 18:50	05/04/20 10:05
5/1/2020	116	123	FGH	YYZ	5/1/20 10:24	5/1/20 10:30	05/01/20 12:10	05/01/20 12:20

Results Table:

Flight Date	Flight #	Tail #	Origin	Destination	Taxi In	Turn	Taxi Out
5/1/2020	111	123	ABC	BCD
5/1/2020	112	123	BCD	EFG	6	51	6
5/1/2020	113	234	CDE	DEF	0	33	4
5/1/2020	114	234	DEF	ZZY	10	127	15
5/1/2020	115	123	EFG	ABC	14	283
5/1/2020	116	123	FGH	YYZ	0	0	0

A couple of explainations about the results:

Flight 111 -  resulted in blanks for all phases of flight because there was not enough data in Table B to determine duration of the phases of flight

Flight 116 - results in zeros instead of blanks because the phases of flight were determined but there was no duration of the equipment in Table A that happened during those phases.

Let me know if you need any additional information.  I appreciate any assistance that you may be able to provide.  

[v-juanli-msft]

Hi @BPS 

When i calculate the phases according to your rules, it comes out with a different result.

But my result seems to be correct for the formula.

Please see my test below.

Turn duration = DATEDIFF(MAX(TableB[IN]),MAX(TableB[OUT]),MINUTE)

Best Regards

Maggie

[BPS]

Hello @v-juanli-msft , 

Thank you for your response.  However I think it is missing something to determine the sum of duration of Table A that happens within the confines of Table B.  When looking at the Flight 112 that you have highlighted here, I would agree that the duration of the turn is 170 minutes however the equipment from Table A was turned off at 5/1/20 3:55 PM which stops the clock. 

I will share a more detailed view of how I mentally do the analysis.  In hopes it will help you or others fully understand what is happening in the example:

When I think through this problem I start with the two tables of of Base Data and I know I am solving for the three Phases each phase has it's own tests so I would first isolate the record in Table B that I want to look at

For this example I will use flight 114 because it is a little more interesting but faily not complex since the related data in Table A is fairly small:

Then lay out the data from the first step and identify the potential start and end times of that phase:

Please let me know if this was helpful or if you still have questions. 

Thanks BPS

[Greg_Deckler]

I'm having a tough time copying and pasting your data for some reason. Can you try following the directions for posting sample data found here: Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Looks like an interesting issue. I can't tell but maybe MTBF could be helpful. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[BPS]

Thank you for your response.  Below I will try to paste in the tables in a different format.  Hopefully this will work better.  Also I will take a look at the other link to see if I can apply that to my problem.  

Table A

Tail,ON,OFF
123,5/1/20 8:06 AM,5/1/20 8:31 AM
123,5/1/20 2:46 PM,5/1/20 3:55 PM
234,5/1/20 4:52 PM,5/1/20 5:29 PM
123,5/1/20 12:00 PM,5/1/20 12:23 PM
123,5/1/20 5:58 PM,5/1/20 10:32 PM

123,5/1/20 9:33 AM,5/1/20 10:01 AM
234,5/1/20 6:02 PM,5/1/20 8:32 PM
234,5/2/20 8:20 AM,5/2/20 8:42 AM
123,5/2/20 12:00 PM,5/2/20 1:00 PM

Table B

Flight Date,Flight #,Tail #,Origin,Destination,ON,IN,OUT,OFF
5/1/2020,111,123,ABC,BCD,,5/1/20 07:47,,05/01/20 08:15
5/1/2020,112,123,BCD,EFG,5/1/20 14:58,5/1/20 15:04,05/01/20 17:54,05/01/20 18:04
5/1/2020,113,234,CDE,DEF,5/1/20 16:06,5/1/20 16:16,05/01/20 17:25,05/01/20 17:35
5/1/2020,114,234,DEF,ZZY,5/1/20 18:21,5/1/20 18:31,05/02/20 08:25,05/02/20 08:40
5/1/2020,115,123,EFG,ABC,5/1/20 18:36,5/1/20 18:50,05/04/20 10:05,
5/1/2020,116,123,FGH,YYZ,5/1/20 10:24,5/1/20 10:30,05/01/20 12:10,05/01/20 12:20

Results Table

Flight Date,Flight #,Tail #,Origin,Destination,Taxi In,Turn,Taxi Out
5/1/2020,111,123,ABC,BCD,,,
5/1/2020,112,123,BCD,EFG,1/6/00 00:00,2/20/00 00:00,5.916666667
5/1/2020,113,234,CDE,DEF,1/0/00 00:00,2/1/00 15:36,4.149999996
5/1/2020,114,234,DEF,ZZY,1/10/00 00:00,5/5/00 16:24,15
5/1/2020,115,123,EFG,ABC,1/14/00 00:00,10/9/00 00:00,
5/1/2020,116,123,FGH,YYZ,1/0/00 00:00,1/0/00 00:00,0