Calculating time gap excluding times that overlap with the ability to filter

walter_564 — Tue, 14 Mar 2023 22:05:38 GMT

I need a calculation which will tells me the number of minutes where there is a gap in vehicle usage times. For example, in the image below:

Yellow represents a length in time where the vehicle is being used, the time where the vehicle is not being used is in grey . I am searching for the number of minutes in grey. The issue is sometimes the times overlap. This also needs to be calculated and filterable per vehicle number.

I found the below link which was very close to what I needed but found the generateseries calculation would not look at a column to calculate lower and upper boundaries rather it needs a specific value.

I have tried to get the start work time by calculating the minimum vehicle start datetime and maximum stop date time through using the calculate function, but I then cant get past the ‘All’ step

https://community.powerbi.com/t5/Desktop/Power-BI-exclude-the-duration-of-overlapping-events-within-a/td-p/2188580

An example of the result expected would be vehicle_number SFT212A where there was a gap. Vehicle stop datetime 2019-04-07 at 05:31. The next soonest time vehicle start datetime occurred was 2019-04-07 07:22. The gap was for 111 mins.

Another example was MLF016A there was the below gap 2019-04-17 14:36 – 2019-04-17 15:31 (55 mins).

Many thanks

	vehicle Start Datetime	vehicle Stop Datetime
Vehicle_number
SFT210A	23/03/2019 21:16	23/03/2019 23:00
SFT210A	23/03/2019 23:00	24/03/2019 7:00
SFT210A	24/03/2019 7:00	24/03/2019 13:34
SFT210A	23/03/2019 21:37	23/03/2019 23:00
SFT210A	23/03/2019 23:00	24/03/2019 7:00
SFT210A	24/03/2019 7:00	24/03/2019 12:13
OOB001A	4/04/2019 15:31	4/04/2019 20:07
OOB001A	4/04/2019 11:12	4/04/2019 15:00
OOB001A	4/04/2019 15:00	4/04/2019 20:18
OOB001A	4/04/2019 11:29	4/04/2019 15:00
OOB001A	4/04/2019 15:00	4/04/2019 21:39
RIL003A	6/04/2019 0:47	6/04/2019 7:00
RIL003A	6/04/2019 7:00	6/04/2019 15:00
RIL003A	6/04/2019 15:00	6/04/2019 18:06
RIL003A	6/04/2019 0:49	6/04/2019 7:00
RIL003A	6/04/2019 7:00	6/04/2019 15:00
RIL003A	6/04/2019 15:00	6/04/2019 18:47
RIL003A	6/04/2019 0:48	6/04/2019 7:00
RIL003A	6/04/2019 7:00	6/04/2019 15:00
RIL003A	6/04/2019 15:00	6/04/2019 19:44
GTT024A	6/04/2019 18:48	6/04/2019 23:00
GTT024A	6/04/2019 23:00	7/04/2019 7:00
GTT024A	6/04/2019 19:27	6/04/2019 23:00
GTT024A	6/04/2019 23:00	7/04/2019 1:45
GTT024A	7/04/2019 7:00	7/04/2019 13:59
GTT024A	6/04/2019 20:49	6/04/2019 23:00
GTT024A	6/04/2019 20:00	6/04/2019 20:30
SFT212A	7/04/2019 2:00	7/04/2019 5:29
SFT212A	7/04/2019 7:22	7/04/2019 15:00
SFT212A	7/04/2019 15:00	7/04/2019 20:14
SFT212A	7/04/2019 2:01	7/04/2019 5:31
SFT212A	7/04/2019 7:29	7/04/2019 15:00
SFT212A	7/04/2019 15:00	7/04/2019 20:16
MN4004A	8/04/2019 15:16	8/04/2019 19:29
MN4004A	8/04/2019 7:58	8/04/2019 15:00
MN4004A	8/04/2019 15:00	8/04/2019 19:44
MN4004A	8/04/2019 7:53	8/04/2019 15:00
MN4004A	8/04/2019 15:00	8/04/2019 19:53
AWE015A	10/04/2019 8:05	10/04/2019 14:31
AWE015A	10/04/2019 8:04	10/04/2019 15:00
AWE015A	10/04/2019 15:00	10/04/2019 20:44
AWE015A	10/04/2019 7:49	10/04/2019 15:00
AWE015A	10/04/2019 15:00	10/04/2019 21:02
MLF016A	17/04/2019 15:31	17/04/2019 20:24
MLF016A	17/04/2019 8:10	17/04/2019 13:39
MLF016A	17/04/2019 7:34	17/04/2019 14:36

Re: Calculating time gap excluding times that overlap with the ability to filter

wdx223_Daniel — Wed, 15 Mar 2023 02:26:31 GMT

GapTime = SUMX ( 'Schedule', VAR _pre = OFFSET ( -1,, ORDERBY ( 'Schedule'[vehicle Start Datetime] ) ) RETURN VAR _pre = MAXX ( WINDOW ( 0, abs, -1, rel, ORDERBY ( 'Schedule'[vehicle Start Datetime], ASC, 'Schedule'[vehicle Stop Datetime] ), PARTITIONBY ( 'Schedule'[Vehicle_number] ) ), 'Schedule'[vehicle Stop Datetime] ) RETURN IF ( _pre > 0 && _pre < 'Schedule'[vehicle Start Datetime], VALUE ( 'Schedule'[vehicle Start Datetime] - _pre ) * 1440 ) )

Re: Calculating time gap excluding times that overlap with the ability to filter

walter_564 — Mon, 20 Mar 2023 20:12:21 GMT

Hi Daniel,

Thanks very much for your reply, I am getting an error message around the below saying the parameter is not the correct type. I have done some research but cant really find an explanation, do you know why this might not work?

Many thanks

Alice

                    ORDERBY (
                        'Schedule'[vehicle Start Datetime], ASC,
                        'Schedule'[vehicle Stop Datetime]
                    ),

topic Re: Calculating time gap excluding times that overlap with the ability to filter in DAX Commands and Tips

Calculating time gap excluding times that overlap with the ability to filter

Re: Calculating time gap excluding times that overlap with the ability to filter

Re: Calculating time gap excluding times that overlap with the ability to filter