03-17-2017 10:12 AM
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.
03-17-2017 11:28 AM
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
03-20-2017 05:14 AM
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?
03-20-2017 09:13 AM
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.
03-21-2017 10:49 PM
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)
03-22-2017 11:40 AM
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
03-28-2017 08:08 AM
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)