Duration between thread times

[Sab]

Hello

I have a table with some duration data (see the table below). I need to calculate the duration with the following conditions:

1. If the time between threads in different tables is overlapping, then the Duration SHOULDN'T be added, but it should just be the duration from Start of the first thread to the End of the last one.

2. If there are pauses at which no thread is running, then the Time in between also SHOULDN'T be counted

3. Of course, the calculation must work for whatever we select on slicers

For ex. If we have 10 threads that worked today in the morning for a total of 10 minutes, and then those same threads worked today afternoon for another 20 minutes, and then in the evening some other threads from other tables worked for 40 minutes, then the total for the two selected tables should be: 10 + 20 + 40 = 70 min

Thank you!

Title	Table Name	Thread ID	Log Start Date	Log Start Time	Log End Date	Log End Time
FI_DOCUMENT	Table1	1	3/31/2023	4:22:45 AM	3/31/2023	4:22:45 AM
FI_DOCUMENT	Table1	2	3/31/2023	4:22:46 AM	3/31/2023	4:22:46 AM
FI_DOCUMENT	Table1	3	3/31/2023	4:22:46 AM	3/31/2023	4:22:46 AM
FI_DOCUMENT	Table1	4	3/31/2023	4:22:47 AM	3/31/2023	4:22:47 AM
FI_DOCUMENT	Table1	5	3/31/2023	4:23:04 AM	3/31/2023	4:23:04 AM
FI_DOCUMENT	Table1	6	3/31/2023	4:23:04 AM	3/31/2023	4:23:04 AM
FI_DOCUMENT	Table1	7	3/31/2023	4:23:09 AM	3/31/2023	4:23:09 AM
FI_DOCUMENT	Table1	8	3/31/2023	4:23:58 AM	3/31/2023	4:23:58 AM
FI_DOCUMENT	Table1	9	3/31/2023	4:24:06 AM	3/31/2023	4:24:06 AM
FI_DOCUMENT	Table1	1	3/31/2023	5:22:45 AM	3/31/2023	5:22:45 AM
FI_DOCUMENT	Table1	2	3/31/2023	5:22:46 AM	3/31/2023	5:22:46 AM
FI_DOCUMENT	Table1	3	3/31/2023	5:22:46 AM	3/31/2023	5:22:46 AM
FI_DOCUMENT	Table1	4	3/31/2023	5:22:47 AM	3/31/2023	5:22:47 AM
FI_DOCUMENT	Table1	5	3/31/2023	5:23:04 AM	3/31/2023	5:23:04 AM
FI_DOCUMENT	Table1	6	3/31/2023	5:23:04 AM	3/31/2023	5:23:04 AM
FI_DOCUMENT	Table1	7	3/31/2023	5:23:09 AM	3/31/2023	5:23:09 AM
FI_DOCUMENT	Table1	8	3/31/2023	5:23:58 AM	3/31/2023	5:23:58 AM
FI_DOCUMENT	Table1	9	3/31/2023	5:24:06 AM	3/31/2023	5:24:06 AM
FI_DOCUMENT	Table1	10	3/31/2023	5:24:27 AM	3/31/2023	5:24:27 AM
FI_DOCUMENT	Table1	11	3/31/2023	5:24:46 AM	3/31/2023	5:24:46 AM
FI_DOCUMENT	Table1	12	3/31/2023	5:24:49 AM	3/31/2023	5:24:49 AM
FI_DOCUMENT	Table1	13	3/31/2023	5:26:02 AM	3/31/2023	5:26:02 AM
FI_DOCUMENT	Table1	14	3/31/2023	5:26:45 AM	3/31/2023	5:26:45 AM
FI_DOCUMENT	Table1	15	3/31/2023	5:26:45 AM	3/31/2023	5:26:45 AM
FI_DOCUMENT	Table1	16	3/31/2023	5:26:48 AM	3/31/2023	5:26:48 AM
FI_DOCUMENT	Table1	17	3/31/2023	5:26:49 AM	3/31/2023	5:26:49 AM
FI_DOCUMENT	Table1	18	3/31/2023	5:28:09 AM	3/31/2023	5:28:09 AM
FI_DOCUMENT	Table1	19	3/31/2023	5:28:15 AM	3/31/2023	5:28:15 AM
FI_DOCUMENT	Table1	20	3/31/2023	5:28:18 AM	3/31/2023	5:28:18 AM
FI_DOCUMENT	Table1	21	3/31/2023	5:28:26 AM	3/31/2023	5:28:26 AM
FI_DOCUMENT	Table1	22	3/31/2023	5:28:35 AM	3/31/2023	5:28:35 AM
FI_DOCUMENT	Table1	23	3/31/2023	5:29:58 AM	3/31/2023	5:29:58 AM
FI_DOCUMENT	Table1	24	3/31/2023	5:29:59 AM	3/31/2023	5:29:59 AM
FI_DOCUMENT	Table1	25	3/31/2023	5:30:36 AM	3/31/2023	5:30:36 AM
FI_DOCUMENT	Table1	26	3/31/2023	5:30:47 AM	3/31/2023	5:30:47 AM
FI_DOCUMENT	Table1	27	3/31/2023	5:30:56 AM	3/31/2023	5:30:56 AM
FI_DOCUMENT	Table1	28	3/31/2023	5:31:55 AM	3/31/2023	5:31:55 AM
FI_DOCUMENT	Table2	1	4/3/2023	8:07:21 PM	4/3/2023	8:07:21 PM
FI_DOCUMENT	Table2	2	4/3/2023	8:07:21 PM	4/3/2023	8:07:21 PM
FI_DOCUMENT	Table2	3	4/3/2023	8:07:21 PM	4/3/2023	8:07:21 PM
FI_DOCUMENT	Table2	4	4/3/2023	8:07:22 PM	4/3/2023	8:07:22 PM
FI_DOCUMENT	Table2	5	4/3/2023	8:07:22 PM	4/3/2023	8:07:22 PM
FI_DOCUMENT	Table2	6	4/3/2023	8:07:22 PM	4/3/2023	8:07:22 PM
FI_DOCUMENT	Table2	7	4/3/2023	8:07:22 PM	4/3/2023	8:07:22 PM
FI_DOCUMENT	Table2	8	4/3/2023	8:07:23 PM	4/3/2023	8:07:23 PM
FI_DOCUMENT	Table2	9	4/3/2023	8:07:24 PM	4/3/2023	8:07:24 PM
FI_DOCUMENT	Table2	10	4/3/2023	8:07:24 PM	4/3/2023	8:07:24 PM

[Sab]

Hi @ryan_mayu 

This works for the sample data, but we need to take two things into account: 

1. Not always the Max thread in the table finishes last, therefore we need to get the Max end time, instead of last value

2. When we have the threads from different tables running in parallel, the total shouldn't add the time like it does now!

Please consider making a call so we fix this together (check DM) 🙂

Thank you!

[Sab]

[Sab]

So for the given dataset, the duration should be: (2 -1) + (4-3) + (6-5)

[ryan_mayu]

@Sab 

pls check if this is what you want.

pls see the attachment below

[Sab]

Hey Ryan, thanks for taking the time to look at it. Please see the screenshot below. It is doing some extra calculations that are not needed. 

We should get only three rows (one for each color)

[ryan_mayu]

@Sab 

there was something wrong in the DAX. i have updated it.

pls see the attachement below