Get total working time from start and end time in rows

[Anonymous]

Hello,

I have a dataset as follow:

Name	Employee Code	Timestamp	Item qty
activity1_start	emp1	10:00	24
activity2_start	emp2	10:15	20
activity1_end	emp1	10:12	24
activity2_end	emp2	11:00	20
activity3_start	emp1	12:00	10
activity3_end	emp1	13:15	10

What I'm trying to do is calculate the actual working time of each employee (12 + 75 mins for emp1; 45 mins for emp2 in this case), then the item/minute for each employee.

Thank you in advance for your help.

[amitchandak]

@Anonymous , please find the attached solution after signature

[Anonymous]

Hi @amitchandak, thank you for the prompt response.

When I open your file, I got the error "Object reference not set to an instance of an object".

Can you please have a look at that again? Thank you!

[amitchandak]

@Anonymous , Please the new column I created

Column = if( right(([Name]),3) = "end" ,datediff( Maxx(FILTER(Sheet1,[Employee Code]=EARLIER(Sheet1[Employee Code]) && [Timestamp] <EARLIER([Timestamp]) && right(([Name]),5) = "start" ),[Timestamp]),[Timestamp],MINUTE),BLANK())

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.

[Anonymous]

Many thanks for your help @amitchandak 

I was able to write another formula based on your idea, without having to use EARLIER:

Time spent on task =

var CurrentEmployee = Sheet[Employee]

var CurrentTimestamp = Sheet[Timestamp]

return

if( right(([Name]),3) = "end", datediff( Maxx(FILTER(Sheet, Sheet[Employee] = CurrentEmployee && Sheet[Timestamp] <= CurrentTimestamp && right(([Name]),5) = "start" ), Sheet[Timestamp]), Sheet[Timestamp],MINUTE),BLANK())