Calculate The Difference Between Consecutive Rows Grouped By Column

In this article, we will learn, how we can find a difference between two consecutive rows group by specific columns in Power BI. Example: Calculate Break Time for Each employee for consecutive Time Stamp Rows

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.

 

 

 

Consider Adele’s timestamps.

 

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.

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:

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!