Post
Ask Question

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.
 
 Calculate Difference Between Consecutive Rows Group By Column
 
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
 
Calculate Difference Between Consecutive Rows Group By Column 
 
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())  
Calculate Difference Between Consecutive Rows Group By Column
 
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.
 
Calculate Difference Between Consecutive Rows Group By Column
 
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!