Difference between rows and category wise sum

[kanikasharma123]

I want to get the difference in time of two rows between out and in and then sum the differences for each category
For example: I have the following columns- ECN_ID, Date, Time, In/Out

I want to create another column called time difference that gives the difference between the rows

The time difference between:
out and in for ECN = 2 and date 5/10/24 is 10 mins, then 1 hour respectively so total = 01:10:00
for ECN=3 and date 5/10/24, first difference is of 5 mins, then 20 mins and then 25 mins so total becomes 00:50:00,
and for ECN=3 and date 6/10/24, first difference is of 5 mins, then 20 mins, then 25 and then 30  mins so total becomes 01:00:00

How to achieve this? Thanks in advance!

[ryan_mayu · Accepted solution]

@kanikasharma123 

you can create a column

Time difference =

VAR _last=maxx(FILTER('Table','Table'[ECN_ID]=EARLIER('Table'[ECN_ID])&&'Table'[date]=EARLIER('Table'[date])&&'Table'[Time]<EARLIER('Table'[Time])&&'Table'[In/Out]="Out"),'Table'[Time])

return if('Table'[In/Out]="In",'Table'[Time]-_last)

then create a measure

Measure = sum('Table'[Time difference])

pls see the attachment below