cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shane7mcdonald
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 / timeMachineWidgets
01/01/20 05:54          A40
01/01/20 05:57A37
01/01/20 06:13A42
01/01/20 06:22A41
2 REPLIES 2
daxer
Solution Sage
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

 

 

AlB
Super User III
Super User III

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 

SU18_powerbi_badge

 

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors