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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

 

 

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors