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
6 Replies
Use datediff function
Column = DATEDIFF('Table'[First],'Table'[Last],HOUR)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
mahoneypat That gives me correct result Pat. TY
Still not giving desired result.
Here is my file: https://www.dropbox.com/s/b0uhe3z8ne4unit/sample%20time%20diff.pbix?dl=0
