calculate the duration between row and previous row (for same user)

[Worstkees]

Hi,
I’m relatively new to DAX and got stuck.

I’m trying to get a measure (or column) in place that calculates the amount of time between the activity of a consumer and his/her previous activity (duration).

Ideally I would have a column (or measure) that gives the duration for a specific id. Where the duration is the time for the specific consumer on that line and the most recent timestamp available for that consumer before this one. For the first yellow entry (consumer 190275) that would mean 1.41.23-1.40.56 0.27 seconds.

Originally I built a unique ranking column per Consumer for all timestamps available, which is working, but even with that in place I get stuck:

Here is my latest but clearly not successful:
Duration =
//timestamp current (TC) - timestamp previous (TP)
VAR PreviousRank = [Ranking]+1)
VAR TC = [Date_time]
VAR Cid = [ConsumerID]
VAR TP = LOOKUPVALUE([Date_time],[Ranking],PreviousRank) // issue is that this is not filtered on specific consumer
return
FILter(table,[consumerid]=Cid && [Ranking] = PreviousRank

Any suggestions how to address this?

[tamerj1]

@Worstkees 

I hope this will work

Duration =
VAR CurrentDateTime = 'Table'[End_Date_Time]
VAR T1 =
    CALCULATETABLE (
        'Table',
        ALLEXCEPT ( 'Table', 'Table'[CustomerID], 'Table'[VariableID] )
    )
VAR T2 =
    FILTER ( T1, [End_Date_Time] < CurrentDateTime )
VAR T3 =
    TOPN ( 1, T2, [End_Date_Time], ASC )
VAR NextDateTime =
    SELECTCOLUMNS ( T3, @DateTime, [End_Date_Time] )
RETURN
    IF ( NOT ISBLANK ( NextDateTime ), CurrentDateTime - NextDateTime )

[Worstkees]

Thanks @tamerj1 ,
I create a column with your suggested code (although also tried as measure).
It runs into an issue with the 

SELECTCOLUMNS ( T3, @DateTime, [End_Date_Time] )

even if I change the @DateTime to @currentDateTime it gives an message that:
The 'CurrentDateTime'parameter could not be resolved because it was referenced in an inner subexpression.
Is there a way arround this?

[tamerj1]

HI @Worstkees 
It could be the name of the vaiable
Try

Duration =
VAR Current_DateTime = 'Table'[End_Date_Time]
VAR T1 =
    CALCULATETABLE (
        'Table',
        ALLEXCEPT ( 'Table', 'Table'[CustomerID], 'Table'[VariableID] )
    )
VAR T2 =
    FILTER ( T1, [End_Date_Time] < Current_DateTime )
VAR T3 =
    TOPN ( 1, T2, [End_Date_Time], ASC )
VAR NextDateTime =
    SELECTCOLUMNS ( T3, @DateTime, [End_Date_Time] )
RETURN
    IF ( NOT ISBLANK ( NextDateTime ), Current_DateTime - NextDateTime )

[tamerj1]

@Worstkees 
Thank you for your reply. Can you please share some sample (dummy) data so I can try from my side?

[Worstkees]

Hi @tamerj1 
I truly appreciate your help here. 
Attaching CSV or XLSX did not work so here is a table:

id ConsumerID Date_time
229772 116363 3/14/2022 8:02
229773 145910 3/14/2022 8:33
229817 145910 3/14/2022 8:57
229820 189803 3/14/2022 10:15
229821 190130 3/14/2022 10:19
229768 190174 3/14/2022 7:22
229769 190177 3/14/2022 7:43
229770 190177 3/14/2022 7:44
229771 190177 3/14/2022 7:44
229774 190186 3/14/2022 8:38
229775 190186 3/14/2022 8:38
229776 190186 3/14/2022 8:38
229777 190186 3/14/2022 8:39
229778 190186 3/14/2022 8:39
229779 190187 3/14/2022 8:40
229819 190217 3/14/2022 10:03
229818 190218 3/14/2022 10:02
229823 190241 3/14/2022 11:40
229824 190241 3/14/2022 11:44
229825 190244 3/14/2022 11:45
229826 190244 3/14/2022 12:01
229827 190253 3/14/2022 12:25

Please keep in mind that the previous ConsumerID activity can be much lower in the table.
So, how do I add a table (that gives me per (unique) ID the amount of time between this entry and the previous entry for the same ConsumerID?

Also with the last suggestion I run into same error message.
Worstkees

[Worstkees]

@tamerj1  sorry, small correction between **'s :
So, how do I add a *column* (that gives me per (unique) ID the amount of time between this entry and the previous entry for the same ConsumerID?

[tamerj1 · Accepted solution]

Hi 
Here is the sample file with the solution https://www.dropbox.com/t/HdxeyDA84DyTOzJq
This is the code of the calculated column. 

Duration = 
VAR Current_DateTime = 'Table'[Date_Time]
VAR T1 =
    CALCULATETABLE (
        'Table',
        ALLEXCEPT ( 'Table', 'Table'[ConsumerID] )
    )
VAR T2 =
    FILTER ( T1, [Date_Time] < Current_DateTime )
VAR T3 =
    TOPN ( 1, T2, [Date_Time], DESC )
VAR NextDateTime =
    SELECTCOLUMNS ( T3, "@DateTime", [Date_Time] )
VAR Result =
    IF ( NOT ISBLANK ( NextDateTime ), Current_DateTime - NextDateTime )
RETURN
    Result

Please let me know if this is the result that you desire.

[Worstkees]

@tamerj1 Not sure what the superlative is for brilliant, perhaps TamarJ1! Thanks

[tamerj1]

Hi @Worstkees 

So for the same ID, the first record is the start of the first activity and the 2nd record is the end of the fist activity. The 3rd record would be the start of the 2nd activity and the 4th record is the end of the 2nd activity. Is that correct? The activity does not have to start and end in the same day. It can start right before midnight and end right after midnight. Am I right?

[Worstkees]

Thanks for asking:
It's about how long it takes before somebody comes back.
You work backwards in time so the order *per consumerid* is
- latest
- latest -1
-latest -2 etc.
The duration for latest -1 is the time between latest and latest-1 (duration latest -1 ends when latest starts).
The duration for latest -2 is the time between latest-1 and latest-2.
The duration for latest -3 is the time between latest-2 and latest-3

Hope this answers,
KR Kees