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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
walter_564
Frequent Visitor

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

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:Diagram example.png

 

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-...

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 Datetimevehicle Stop Datetime
Vehicle_number
SFT210A23/03/2019 21:1623/03/2019 23:00
SFT210A23/03/2019 23:0024/03/2019 7:00
SFT210A24/03/2019 7:0024/03/2019 13:34
SFT210A23/03/2019 21:3723/03/2019 23:00
SFT210A23/03/2019 23:0024/03/2019 7:00
SFT210A24/03/2019 7:0024/03/2019 12:13
OOB001A4/04/2019 15:314/04/2019 20:07
OOB001A4/04/2019 11:124/04/2019 15:00
OOB001A4/04/2019 15:004/04/2019 20:18
OOB001A4/04/2019 11:294/04/2019 15:00
OOB001A4/04/2019 15:004/04/2019 21:39
RIL003A6/04/2019 0:476/04/2019 7:00
RIL003A6/04/2019 7:006/04/2019 15:00
RIL003A6/04/2019 15:006/04/2019 18:06
RIL003A6/04/2019 0:496/04/2019 7:00
RIL003A6/04/2019 7:006/04/2019 15:00
RIL003A6/04/2019 15:006/04/2019 18:47
RIL003A6/04/2019 0:486/04/2019 7:00
RIL003A6/04/2019 7:006/04/2019 15:00
RIL003A6/04/2019 15:006/04/2019 19:44
GTT024A6/04/2019 18:486/04/2019 23:00
GTT024A6/04/2019 23:007/04/2019 7:00
GTT024A6/04/2019 19:276/04/2019 23:00
GTT024A6/04/2019 23:007/04/2019 1:45
GTT024A7/04/2019 7:007/04/2019 13:59
GTT024A6/04/2019 20:496/04/2019 23:00
GTT024A6/04/2019 20:006/04/2019 20:30
SFT212A7/04/2019 2:007/04/2019 5:29
SFT212A7/04/2019 7:227/04/2019 15:00
SFT212A7/04/2019 15:007/04/2019 20:14
SFT212A7/04/2019 2:017/04/2019 5:31
SFT212A7/04/2019 7:297/04/2019 15:00
SFT212A7/04/2019 15:007/04/2019 20:16
MN4004A8/04/2019 15:168/04/2019 19:29
MN4004A8/04/2019 7:588/04/2019 15:00
MN4004A8/04/2019 15:008/04/2019 19:44
MN4004A8/04/2019 7:538/04/2019 15:00
MN4004A8/04/2019 15:008/04/2019 19:53
AWE015A10/04/2019 8:0510/04/2019 14:31
AWE015A10/04/2019 8:0410/04/2019 15:00
AWE015A10/04/2019 15:0010/04/2019 20:44
AWE015A10/04/2019 7:4910/04/2019 15:00
AWE015A10/04/2019 15:0010/04/2019 21:02
MLF016A17/04/2019 15:3117/04/2019 20:24
MLF016A17/04/2019 8:1017/04/2019 13:39
MLF016A17/04/2019 7:3417/04/2019 14:36
2 REPLIES 2
wdx223_Daniel
Super User
Super User

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
            )
)

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]
                    ),

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.