Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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 wo...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
v-caliao-msft
Microsoft Employee
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

       

       

      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's avatar
        MarcelBeug
        Community Champion
        9 years ago

        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.