cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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

1 ACCEPTED SOLUTION
Super User

``````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``````

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

13 REPLIES 13
Resolver II

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!

Regular Visitor

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

Community Support

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.

Helper III

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

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``````

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Helper III

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

Frequent Visitor

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

Super User

@Josh97Ellis you have to change colum formatting.

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

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Helper III

@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

Super User

``````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``````

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors