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     🙂

     

     

lit2018pbi's avatar
lit2018pbi
Resolver II
6 years ago

Hi Josh,

 

Step 1: You can add a new column tp concatenate date and time using the following Dax

            Date and Time = CONCATENATE('time diff'[Date].[Date]&" ",'time diff'[Time])

 Step 2: Now find the Lag Date and Time using the following Dax

     Lag Date and Time = CALCULATE(MAX('time diff'[Date and Time]),FILTER('time diff','time diff'[Material] = EARLIER('time diff'[Material])&&'time diff'[Date and Time]<EARLIER('time diff'[Date and Time])))

 Step 3: Find out the time difference using the following Column

            Difference in Minutes = DATEDIFF('time diff'[Lag Date and Time],'time diff'[Date and Time],MINUTE)

 

There are many steps involved here but you will get the result by implementing the above!
Thanks!
 
  • CPL_'s avatar
    CPL_
    Regular Visitor
    2 years ago

    This was so helpful for me! I needed to calculate a time difference based on an ID, and this did so perfectly!