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 

 

Try this 

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



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  This is close, it looks like it needs to be "shifted up" one row. So basically, the value I get in row 2 should be the value for row 1, etc.. You'll see that in the picture. 

     

     

    • 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

    • waleb's avatar
      waleb
      Frequent Visitor
      3 years ago

      pls, I am also stuck here. The value in row 2, should be in row 1