Calculating time duration

[Alanoudfahad]

Hi!

I have a data for employees where they check in and check out everytime they enter the building. I want to calculate the time duration per day (I have a date slicer)

For instance here I want to subtract 5:55pm from 7:59am to get 9hrs 56min total hours.

I appreciate any help!

[Jihwan_Kim · Accepted solution]

Hi, @Alanoudfahad 

Please check the below picture and the measure.

Result =

VAR currentid =

MAX ( Data[ID] )

VAR currentdate =

MAX ( Data[Date] )

VAR minintime =

CALCULATE (

MIN ( Data[Time] ),

FILTER (

ALL ( Data ),

Data[ID] = currentid

&& Data[Date] = currentdate

&& Data[In/Out] = "In"

)

)

VAR maxouttime =

CALCULATE (

MAX ( Data[Time] ),

FILTER (

ALL ( Data ),

Data[ID] = currentid

&& Data[Date] = currentdate

&& Data[In/Out] = "Out"

)

)

RETURN

maxouttime - minintime

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Alanoudfahad]

Works wonderfully, thanks!

[amitchandak]

@Alanoudfahad , Try a new column like

var _max = if([In/out] = "Out" , max(filter(Table, [Date] =earlier([Date]) && [Id] =earlier([ID]) && [In/out] ="In" && [Time] <earlier([Time])),[Time]), blank())
return
if([In/out] = "Out" && not(isblank(_max)) , datediff(_max, [Time], minute))/60

Ideally in place of time you should use date time column

date time  =[date]+[time]

[Alanoudfahad]

Hi @amitchandak It gives me this error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." even after crreating date/time column