Use Power Query to Subtract time frames form a complete day

[Zneta]

Hello everybody,

There might be an easier solution to my problem, or to not do it in Power Query at all. Any help is appreciated:

I have a Dataset with start and end times like this:

Start: 10:30 am, End: 10:33 am

Start: 10:31 am, End 10:45 am

See the picture below.

Many of these timeframes overlap each other. I want a way to show me the time (in seconds or hours) of a full 24 hour day, where non of these time frames happend. So if a day has 1440 minutes and i only have two time frames (10:30 am to 10:45 am and 10:40 am to 10:45 am) i want the result to be 1425 minutes (and not 1420).

I hope this makes sense.

Thanks

[Zneta]

Thank you, this is a great starting point. I followed your steps but for some reason the Fault Durations are not being added up. For example the first two Faults are within the same time frame. So the first faultduration should read 1696 as it is 1696 seconds long. The second FaultDuration should also read 1696 since that fault is in the time frame of the first and wont add to the total. why does it only show 5? unlike in your example.

[v-yangliu-msft]

Hi  @Zneta ,

Here are the steps you can follow：

1. Create calculated column.

Date =
DATE(YEAR('Table'[Fault start]),MONTH('Table'[Fault start]),DAY('Table'[Fault start]))

Minute =
var _mindate=
MINX(FILTER(ALL('Table'),'Table'[Date]=EARLIER('Table'[Date])),[Fault start])
var _maxdate=
MAXX(FILTER(ALL('Table'),'Table'[Date]=EARLIER('Table'[Date])),[Fault end])
return
DATEDIFF(
    _mindate,_maxdate,MINUTE)

2. Create measure.

Measure =
var _mindate=MAX('Table'[Date]) + TIME(0,0,0)
var _maxdate=_mindate +1
var _oneday=
DATEDIFF(
    _mindate,_maxdate,MINUTE)
return
_oneday - MAX('Table'[Minute])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly