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 for all rows. Here's what I have tried, but there is not enough memory to complete the operation. I am wondering if anyone knows a better way around this.

🙂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 __diffDid I answer your question? Mark my post as a solution!Appreciate with a kudos 🙂

Calculating time between two rows in the same column

13 Replies

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_
  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!
amitchandak
Super User
6 years ago
Anonymous , Try like

diff =
var _max = maxx(filter(Table, [date] = earlier([date]) && [time] < earlier([time]) && [material] = earlier([material])),[time])
return
[time] - maxx(filter(Table, [date] = earlier([date]) && [time] = _max && [material] = earlier([material])),[time])
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
  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
    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 🙂
Greg_Deckler
Community Champion
6 years ago
Anonymous - See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
v-lionel-msft
Community Support
6 years ago
Hi Anonymous ,

Like this?

Or like this?

Or nandukrishnavs 's formula?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Anonymous
  Not applicable
  6 years ago
  v-lionel-msft Hi, I need it to be the number of minutes (duration) between each time stamp. So in excel, it would look like this: