Calculate duration by substracting values in different rows for the same value in one column

[Anonymous]

Hi All, 

I would like to calculate the duration based on the data below for each of the ConversationId. I would like to then calculate the Avg Duration per conversation as well as having the extra column with Duration for each fo the ConversationId. 

How can I best do it? 

Please check the data structure below:

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

At first, you need to create a new column "rank".

rank =
RANKX ( test, test[CreatedDate],, ASC, DENSE )

Then create a new column to calculate the duration.

duration =
VAR a =
    CALCULATE (
        FIRSTNONBLANK ( test[CreatedDate], 1 ),
        FILTER ( test, test[rank] = EARLIER ( test[rank] ) + 1 )
    )
VAR b =
    IF ( ISBLANK ( a ), NOW (), a )
RETURN
    DATEDIFF ( test[CreatedDate], b, SECOND )

Now, you can create a new measure to get the average with the AVERAGE() function and use visuals to show it.

[Anonymous]

Here's a way to do this in Power Query:

1) Create two index columns: Index1 should start at 1, Index2 at 0. You can do this via the User Interface. Important: you have to sort by date before doing so for this to work.

2) Merge your query to itself by the indices and expand the resulting table. You should now have two date columns and two conversationid columns.

3) Now you can simply substract those dates from each other to get the duration for every row. Nest this into an if-statement: if conversationid1 = conversationid2 then date1 - date2 else null.

4) To get the overall duration, group this table by conversationid. The sum of the durations for each row is the overall duration of the conversation.

5) For calculating averages etc., simply use the corresponding functions in DAX.

I have assumed that there can only be one conversation at a time and that it has to end for the next conversation to start. If this is not the case: Sort your table by date and conversationid before using above method.

I hope this helps; might not be the optimal solution but it should work.