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 PM | Jill | Pick |
| 3 | 1:24:59 PM | Harry | Pick |
| 1 | 1:24:09 PM | Bob | Pick |
| 2 | 1:23:27 PM | Jill | Pick |
| 3 | 1:20:51 PM | Harry | Pick |
What I want to show is the time differences between each row with that changing dynamically as the table is filtered like so:
| TransID | ModAt | ModBy | EventID | TimeDiff(In Seconds) |
| 1 | 1:27:20 PM | Bob | Pick | 21 |
| 1 | 1:26:59 PM | Bob | Pick | 11 |
| 2 | 1:26:48 PM | Jill | Pick | 24 |
| 3 | 1:26:24 PM | Harry | Pick | 45 |
| 2 | 1:25:39 PM | Jill | Pick | 40 |
| 3 | 1:24:59 PM | Harry | Pick | 50 |
| 1 | 1:24:09 PM | Bob | Pick | 42 |
| 2 | 1:23:27 PM | Jill | Pick | 156 |
| 3 | 1:20:51 PM | Harry | Pick | 0 |
Then Filtered:
| TransID | ModAt | ModBy | EventID | TimeDiff(In Seconds) |
| 1 | 1:27:20 PM | Bob | Pick | 21 |
| 1 | 1:26:59 PM | Bob | Pick | 170 |
| 1 | 1:24:09 PM | Bob | Pick | 0 |
Thanks!
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 )
6 Replies
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 )
