cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculating time difference before and after a specific time

Hi, I'm a bit stumped on this problem and was hoping for some help.

I need to find the difference between the last date/time BEFORE 6am, and the first date/time AFTER 6am. I'm trying to find out the time it takes for 2 shifts to handover in the morning. In this case it would be 16 mins. Any help would be much appreciated 😀.

 Date / time Machine Widgets 01/01/20 05:54 A 40 01/01/20 05:57 A 37 01/01/20 06:13 A 42 01/01/20 06:22 A 41
2 REPLIES 2
Solution Sage

``````// Assuming you have a table
// with columns Datetime and Machine.
// When you select a machine,
// you want to know the diff
// between the two closest times
// to 6am, one before and one after it.
// If more than 1 machine is selected,
// BLANK should be returned. Bear in
// mind that the measure honours all
// the currently set filters.
// Let the table be T.

[Diff (min)] =
var __onlyOneMachineVisible = HASONEVALUE( T[Machine] )
var __minDate = INT( MIN( T[Datetime] ) )
var __maxDate = INT( MAX( T[Datetime] ) )
var __onlyOneDayVisible = ( __minDate = __maxDate )
var __canCalculate = true()
&& __onlyOneMachineVisible
&& __onlyOneDayVisible
var __output =
if( __canCalculate,

var __closestBefore6am =
CALCULATE(
MAX( T[Datetime] ),
KEEPFILTERS(
// We make use of the fact
// that one can compare datetimes
// with real numbers since a date
// under the hood is a real number,
// where the integer part stands for
// the day and the fractional part
// stands for the hour.
T[Datetime] <= __minDate + .25
)
)
var __closestAfter6am =
CALCULATE(
MIN( T[Datetime] ),
KEEPFILTERS(
T[Datetime] > __minDate + .25
)
)
var __shouldCalculate =
and(
not ISBLANK( __closestBefore6am ),
not ISBLANK( __closestAfter6am )
)
var __diff =
if( __shouldCalculate,
DATEDIFF(
__closestBefore6am,
__closestAfter6am,
MINUTE
)
)
return
__diff
)
return
__output``````

Super User III

Where do you want this, in a measure? If so:

``````Measure =
VAR date_ =
INT ( MAX ( Table1[DateTime] ) )
VAR lastBefore6_ =
CALCULATE (
MAX ( Table1[DateTime] ),
Table1[DateTime] < ( date_ + ( 6 / 24 ) )
)
VAR firstAfter6_ =
CALCULATE (
MIN ( Table1[DateTime] ),
Table1[DateTime] >= ( date_ + ( 6 / 24 ) )
)
VAR diffInMinutes_ = ( firstAfter6_ - lastBefore6_ ) * 24 * 60
RETURN
diffInMinutes_``````

Take into account though that the code above is tailored to the table you show. I guess you are looking for a more general result, like the average of that time difference across days, etc. You'll have to tweak it a bit for that, but the main logic will be the same.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.