Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating time between two rows in the same column

Hi,    I am trying to create a new column that displays the how many minutes have passed between each row in the [Time] Column. Basically, I just need to subtract the bottom row from the above row ...
Need Help
Tips and Tricks
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

     ğŸ™‚Anonymous 

     

    Diff =
VAR __Previous =
    MINX (
        FILTER (
            'Table',
            'Table'[Date] = EARLIER ( 'Table'[Date] )
                && 'Table'[Time] > EARLIER ( 'Table'[Time] )
        ),
        'Table'[Time]
    )
VAR __diff = __Previous - 'Table'[Time]
RETURN
    __diff



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

     

     

nandukrishnavs's avatar
nandukrishnavs
Community Champion
6 years ago

Anonymous you have to change colum formatting.

Set the data type as Time. Format as HH:mm



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Anonymous's avatar
Anonymous
Not applicable
6 years ago

nandukrishnavs Yeah, i got the format correct after I sent that, I was just in the wrong place. 

 

How can I adjust the formula so that each row in that calculated column is essentially shifted up one row. per my last reply. 

 

Thanks

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

     ğŸ™‚Anonymous 

     

    Diff =
VAR __Previous =
    MINX (
        FILTER (
            'Table',
            'Table'[Date] = EARLIER ( 'Table'[Date] )
                && 'Table'[Time] > EARLIER ( 'Table'[Time] )
        ),
        'Table'[Time]
    )
VAR __diff = __Previous - 'Table'[Time]
RETURN
    __diff



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

     

     

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      Good afternoon @nandukrishnavs.

      I have a similar situation. I tried the two recommended solutions, resulting in what is shown in the following image:

      RichardFig1_0-1632865434549.png

      Sometimes it works, so the solution I need should not be far away. To explain my case, each row represents a movement (or modification of the ticket) this group for a help desk ticket (14 movements). I have a column with the date and another with the modification time. I also concatenated both columns in the "Date & Time" column.

      I need to calculate how much time passed between each movement. For example, from the first move to the second passed 00h:40m:59s.

      The result can be in duration format or could indicate the number of seconds in total and then convert it to minutes or hours.

      I hope you can help me.

      Best regards

      Richard