topic Re: Calculating time difference before and after a specific time in DAX Commands and Tips

Calculating time difference before and after a specific time

shane7mcdonald — Mon, 16 Nov 2020 09:18:56 GMT

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

Re: Calculating time difference before and after a specific time

AlB — Mon, 16 Nov 2020 09:25:35 GMT

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

Re: Calculating time difference before and after a specific time

Anonymous — Mon, 16 Nov 2020 12:30:05 GMT

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