Reply
Regular Visitor
Posts: 29
Registered: ‎01-03-2017

datediff in columns with a equal id

 

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. Smiley Happy

 

 

image.png

Super Contributor
Posts: 1,815
Registered: ‎08-11-2015

Re: datediff in columns with a equal id

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

Regular Visitor
Posts: 29
Registered: ‎01-03-2017

Re: datediff in columns with a equal id

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

 

Senior Member
Posts: 350
Registered: ‎11-25-2016

Re: datediff in columns with a equal id

Take a look at this thread.

 

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

Specializing in Power Query Formula Language (M)
Regular Visitor
Posts: 29
Registered: ‎01-03-2017

Re: datediff in columns with a equal id

Thanks.

 

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

 

 

Regular Visitor
Posts: 29
Registered: ‎01-03-2017

Re: datediff in columns with a equal id

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

Highlighted
Senior Member
Posts: 350
Registered: ‎11-25-2016

Re: datediff in columns with a equal id

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.

Specializing in Power Query Formula Language (M)
Moderator
Posts: 549
Registered: ‎03-06-2016

Re: datediff in columns with a equal id

Hi @rmachado,

 

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)

Capture.PNG

 

Regards,

Charlie Liao

 

Regular Visitor
Posts: 29
Registered: ‎01-03-2017

Re: datediff in columns with a equal id

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