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!
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]