datediff in columns with a equal id

[Anonymous]

I'm trying to calculate the date difference between rows that have the same issueid in the case we have more than one entry. I tried to use a method that I searched here but I could not make it work. Can someone help? If it was plain code it would be easy. 🙂

[Sean]

What exactly is the desired outcome? You have 8 rows with 14318 issueid what should be the result?

[Anonymous]

The ideia is to get the diff between each row with that id. This table in fact is a status change history table. So what I need is to get the time an issueid remain in that state.

Using issueid 14318 it would be something like

20 sec

9 sec

3 sec

5 sec

1m52sec

4 sec

empty

[v-caliao-msft]

Hi @Anonymous,

You could achieve this requirement by the calculated columns below.

Index = RANKX(FILTER(Table1,Table1[ISSUEID]=EARLIER(Table1[ISSUEID])),Table1[Create],,ASC)
PreviousTime = LOOKUPVALUE(Table1[Create],Table1[ISSUEID],Table1[ISSUEID],Table1[Index],Table1[Index]-1)
datediff = DATEDIFF(Table1[PreviousTime],Table1[Create],SECOND)

Regards,

Charlie Liao

[Anonymous]

Thanks @v-caliao-msft

In fact now I have the diff correctly, but strugling to the fact that If I display the issue by the state it entered the time is from the last state and not the one it entered..

I have to figure how to match the state with is duration

[Anonymous]

Hello again @v-caliao-msft and @MarcelBeug

I'm still strugling with this one because I can not manage to display the current (not closed State).

As you can see in the above image I manage to use the formulas provided in the topic and create an index based on start date for each status.
The problem is the end date for the status that is displayed on the rigth but not for the first status. How do I get the end date in the correct status and no end date in the running status (in the image with would be (scoping pipeline)

Thanks

[MarcelBeug]

In the solution I provided, data from the current row was merged with data from the previous row.

Now it looks like you want to merge data from the current row with the data from the next row.

You can adjust the step in which the tables are merged: instead of merging the table on Index.0 first and Index.1 next, you need to merge the table on Index.1 first and Index.0 next.

[Anonymous]

Ok. I will try.

[MarcelBeug]

Take a look at this thread.

In PowerQuery, "duration" is pretty much equivalent with "datediff".

[Anonymous]

Hello Again @MarcelBeug

I'm struggling with the index on date time, because I already have that one in the correct form. So i'm not quite understanding the need for that index.

Can you help me on that one?

Thanks

[MarcelBeug]

In the link I provided, you can find a step by step explanation in my post directly under "All Replies".

In general, when you need to calculate something based on data that is on different rows, a very efficient way of doing that is to get all required data on the same row,

So first you sort your data in such a way that the data you need are on subsequent rows, so in your case when you sort on issueid and created, you need the timestamp in the current row and the timestamp in the previous row (and you also need the issueid from both rows to verify if it is still the same issueid).

Next steps, in order to get the data on the some row, you add an index starting with 0 and an index starting with 1, then you merge the table with itself (giving you a "NewColumn" with nested tables), rename this column to "Previous", expand the column with the columns you need (so "issueid" and "created"), keeping the column name as prefix and then you can add a column in which you subtract "Previous.created"  from "created" if "Previous.issueid"  = "issueid" (else null).

You can watch the first minute of this video in which I did exactly the same for another question.

[Anonymous]

Thanks.

Looking into it, but it will be tricky for me since I do not know much of powerquery.