cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manoymon
Helper I
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.

 

 

powerbi.JPG

 

 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.

View solution in original post

4 REPLIES 4
Trotzuk
Advocate I
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?

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.

View solution in original post

Thank you very much.

 

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

 

Have a great day!!!  Smiley Very Happy

Helpful resources

Announcements
User Groups Public Preview

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.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

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.