Forum Discussion

Alanoudfahad's avatar
Alanoudfahad
Frequent Visitor
5 years ago
Solved

Calculating time duration

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...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
    Alanoudfahad
    Frequent Visitor
    5 years ago

    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