Forum Discussion
Time difference between first and last time
Hi, Can't get what I'm looking for exactly from other posts. Need to calculate the time difference in hours between the 'Time of Last Call' & the 'Time of First Call' for each 'carer_id' ...
Here is a measure expression (not a column) that gives your expected results.
TimeDiff =
VAR thisdate =
MIN ( data[Date] )
VAR thisfirst =
MIN ( data[Time of First Call] )
VAR prevdate =
CALCULATE (
MAX ( data[Date] ),
ALL ( data ),
VALUES ( data[carer_id] ),
data[Date] < thisdate
)
VAR prevlast =
CALCULATE (
MAX ( data[Time of Last Call] ),
ALL ( data ),
VALUES ( data[carer_id] ),
data[Date] = prevdate
)
RETURN
IF (
NOT ( ISBLANK ( prevdate ) ),
24
* FIXED ( ( thisdate + thisfirst ) - ( prevdate + prevlast ), 3 )
)Pat
