cancel
Showing results for
Did you mean:
Helper I

## Time Difference in Different Rows

Hello,

I am stuck at the moment trying to calculate the difference between 2 times residing in different rows.

Basically, if the name and date are the same and there are multiple instance of in_time and out_time, I want to calculate the differencebetween the latest OUT_TIME and earliest IN_TIME.

Thank you very much in advance.

1 ACCEPTED SOLUTION
`Difference = sumx(summarize(table1, Table1[Name], Table1[Date], "MaxOUT", max(Table1[OUT_TIME])),[MaxOUT])-sumx(summarize(table1, Table1[Name], Table1[Date], "MinIN", min(Table1[IN_TIME])), [MinIN])`

This code creates two summary tables grouped on the name and date: one for the minimum IN_TIME and one for the maximum OUT_TIME. The IN_TIME is then subtracted from the OUT_TIME to get the difference.

This will return the difference for each Name/Date grouping for each row. If you want unique values you would need to create a summary table that was only the Name, Date and Difference columns.

4 REPLIES 4

Are you wanting to return the difference in a measure or in a row of the table?

Also, how do you want to represent the difference? Total hours? total minutes?

Helper I

Thank you for the quick reply.

I knew I forgot something.  I want the difference to show as a decimal value.

`Difference = sumx(summarize(table1, Table1[Name], Table1[Date], "MaxOUT", max(Table1[OUT_TIME])),[MaxOUT])-sumx(summarize(table1, Table1[Name], Table1[Date], "MinIN", min(Table1[IN_TIME])), [MinIN])`

This code creates two summary tables grouped on the name and date: one for the minimum IN_TIME and one for the maximum OUT_TIME. The IN_TIME is then subtracted from the OUT_TIME to get the difference.

This will return the difference for each Name/Date grouping for each row. If you want unique values you would need to create a summary table that was only the Name, Date and Difference columns.

Helper I

Thank you very much.

As a beginner, you really saved me a ton of time and headaches.

Have a great day!!!

Announcements