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 )
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 )
Sorry, but one last question. Is there a way to do that as a calculated column vs. a measurement?
Sure, you can just add a column to your table and make it = the measure.
Column 2 = [TimeDiff(In Seconds) TransID]