cancel
Showing results for
Search instead 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  Advocate I
`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  Advocate I

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.  Advocate I
`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!!!   ## Helpful resources

Announcements #### Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview. #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (4,399)