# Calculate The Difference Between Consecutive Rows Grouped By Column

## Overview

In this article, we will learn how we can find the difference between two consecutive rows grouped by specific columns in Power BI.

## Real Life Example

We came across the following scenario.

Consider the following example where we have an In-Out time pair for each employee’s attendance data. Now we need to measure the Break time for each employee throughout the day.

We have three pairs of In-Out times. We want to calculate break time by calculating the difference between each pair, meaning the first row’s out time and the next row's start time.

Break time for Adele = First Row’s Out Time – Next Row’s In Time

So, how can we achieve this for each employee?

Let’s get started.

Step 1

Add Index Column in Power Query

Step 2

Add Next Row’s Start time as a column using the Look Up formula. This will give us the following data.
1. Next Start Time = IF(Attendance[Full Name] = LOOKUPVALUE(Attendance[Full Name],Attendance[NewIndex],Attendance[NewIndex]+1),LOOKUPVALUE(Attendance[Start Time],Attendance[NewIndex],Attendance[NewIndex]+1),BLANK())

Step 3

Create Measure using the following formula:
1. Break Time (Minutes) = (DATEDIFF(Attendance[End Time],Attendance[Next Start Time],SECOND)/60)+0
Now, add everything to the same table.

So, we have the following output.

I hope this helps you!

## Conclusion

This is how we can find the difference between consecutive rows grouped by a specific column.

Happy Reporting!

Stay connected with me!