Forum Discussion
shane7mcdonald
5 years agoFrequent 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 ...
Anonymous
5 years agoNot 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