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 here I want to subtract 5:55pm from 7:59am to get 9hrs 56min total hours.

 

I appreciate any help!

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

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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