Forum Discussion
Solved
DateDiff between filtered rows
Hi All, I have a table like so: TransID ModAt ModBy EventID 1 1:27:20 PM Bob Pick 1 1:26:59 PM Bob Pick 2 1:26:48 PM Jill Pick 3 1:26:24 PM Harry Pick 2 1:25:39...
Hello STS_Joshua
Give this a try.
TimeDiff(In Seconds) = VAR CurrentRowTime = SELECTEDVALUE ( 'Table'[ModAt] ) VAR TimeSec = DATEDIFF ( CALCULATE ( MAX ( 'Table'[ModAt] ), ALLSELECTED ( 'Table' ), 'Table'[ModAt] < CurrentRowTime ), CurrentRowTime, SECOND ) RETURN IF ( ISBLANK( TimeSec ), 0, TimeSec )That's odd, the same change works for me:
TimeDiff(In Seconds) TransID = VAR CurrentRowTime = SELECTEDVALUE ( 'Table'[ModAt] ) VAR TransID = SELECTEDVALUE ( 'Table'[TransID] ) VAR TimeSec = DATEDIFF ( CALCULATE ( MAX ( 'Table'[ModAt] ), ALLSELECTED ( 'Table' ), 'Table'[ModAt] < CurrentRowTime, 'Table'[TransID] = TransID ), CurrentRowTime, SECOND ) RETURN IF ( ISBLANK( TimeSec ), 0, TimeSec )
Hello STS_Joshua
Give this a try.
TimeDiff(In Seconds) =
VAR CurrentRowTime = SELECTEDVALUE ( 'Table'[ModAt] )
VAR TimeSec =
DATEDIFF (
CALCULATE ( MAX ( 'Table'[ModAt] ),
ALLSELECTED ( 'Table' ),
'Table'[ModAt] < CurrentRowTime ),
CurrentRowTime, SECOND )
RETURN
IF ( ISBLANK( TimeSec ), 0, TimeSec )Thanks for the answer, I have a followup though. Is there a way to display the DateDiff grouped for each order when unfiltered? Like so:
TransID ModAt ModBy EventID TimeDiff(In Seconds) 1 1:27:20 PM Bob Pick 21 1 1:26:59 PM Bob Pick 170 2 1:26:48 PM Jill Pick 24 3 1:26:24 PM Harry Pick 45 2 1:25:39 PM Jill Pick 132 3 1:24:59 PM Harry Pick 248 1 1:24:09 PM Bob Pick 0 2 1:23:27 PM Jill Pick 0 3 1:20:51 PM Harry Pick 0 I tried changing your calculation some but did not get what I wanted:
TimeDiff = VAR CurrentRowTime = SELECTEDVALUE ( 'Table'[ModAt] ) VAR CurrentTransID = SELECTEDVALUE( 'Table'[TransID]) VAR TimeSec = DATEDIFF ( CALCULATE ( MAX ('Table'[ModAt]), 'Table'[TransID]=CurrentTransID, 'Table'[ModAt] < CurrentRowTime ), CurrentRowTime, SECOND ) RETURN IF ( ISBLANK( TimeSec ), 0, TimeSec )That's odd, the same change works for me:
TimeDiff(In Seconds) TransID = VAR CurrentRowTime = SELECTEDVALUE ( 'Table'[ModAt] ) VAR TransID = SELECTEDVALUE ( 'Table'[TransID] ) VAR TimeSec = DATEDIFF ( CALCULATE ( MAX ( 'Table'[ModAt] ), ALLSELECTED ( 'Table' ), 'Table'[ModAt] < CurrentRowTime, 'Table'[TransID] = TransID ), CurrentRowTime, SECOND ) RETURN IF ( ISBLANK( TimeSec ), 0, TimeSec )My mistake was replacing your ALLSELECTED filter in CALCULATE. With that in there it works. Thanks!
