Calculating time between two rows in the same column

[Josh97Ellis]

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. 

[lit2018pbi]

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_]

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

[v-lionel-msft]

Hi @Josh97Ellis ,

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.

[Josh97Ellis]

@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:

[Greg_Deckler]

@Josh97Ellis - 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/3395...

[nandukrishnavs]

@Josh97Ellis 

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 🙂

[Josh97Ellis]

@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. 

[waleb]

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

[nandukrishnavs]

@Josh97Ellis 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 🙂

[Josh97Ellis]

@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 · Accepted solution]

 🙂@Josh97Ellis 

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]

Good afternoon @nandukrishnavs.

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

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

[amitchandak]

@Josh97Ellis , 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])