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 work. Can someone help? If it was plain code it would be easy. :)

 

 

Need Help

11 Replies

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

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

    • 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