DateDiff between filtered rows

[STS_Joshua]

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!

[jdbuchanan71 · Accepted solution]

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 )

[jdbuchanan71 · Accepted solution]

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 )

[STS_Joshua]

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 )

[jdbuchanan71 · Accepted solution]

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 )

[STS_Joshua]

Sorry, but one last question. Is there a way to do that as a calculated column vs. a measurement? 

[jdbuchanan71]

Sure, you can just add a column to your table and make it = the measure.

Column 2 = [TimeDiff(In Seconds) TransID]

[STS_Joshua]

My mistake was replacing your ALLSELECTED filter in CALCULATE. With that in there it works. Thanks!