Calculating time difference before and after a specific time

[shane7mcdonald]

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

[Anonymous]

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

[AlB]

Hi @shane7mcdonald 

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