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
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?
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.
You could achieve this requirement by the calculated columns below.
Index = RANKX(FILTER(Table1,Table1[ISSUEID]=EARLIER(Table1
PreviousTime = LOOKUPVALUE(Table1[Create],Table1[ISSUEID],Table1[
datediff = DATEDIFF(Table1[PreviousTime],Table1[Create],SECON
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