Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How can I track multiple time differences simultaneously based on another column?

Hi all,

 

I need help in tracking multiple smiultaneous timestamps.

 

I have a simple table like the one attached.

Table with columns Person ID, Time, Worktype and Index

 

This is a table that is created when an employee (Person ID column) logs when starting to work at a particular position (Worktype column), combines positions, splits positions, goes on a break or goes home.

 

I need to track both the total time an employee has worked in any position (POSITION-A, B, SOUTH, WEST etc.) and the time worked in each position separately and also the time spent on breaks.

 

Combining and splitting of positions complicates things. For example an employee might start working at POSITION-A, then combine POSITION-A and B, then continue only on POSITION-A and then go on a BREAK. In this scenario the employee should have working time at POSITION-A for the entire session and at POSITION-B only from the period that the positions were combined. There are also several options instead of a BREAK where the employee can go to, for example OFFICE or OFFWORK.

 

I'm able to calculate the time difference between two consecutive Worktype stamps of the same person with the following code.

 

Elapse btw stamp and previous stamp (min) = 

VAR _curTime =
    myTable[Time]

VAR _pasttime =
    CALCULATE (
        Max(myTable[Time]) ,
        myTable[Time] < _curTime,  
        ALLEXCEPT (myTable, myTable[Person ID])
        )

VAR _dif = 
    IF(ISBLANK(_pasttime), 0 ,DATEDIFF ( _pasttime, _curTime, MINUTE ))

RETURN_dif

This is what the table looks like at the moment.

Table with "Elapse btw stamp and previous stamp"

 

What I can't figure out is how to put the elapsed time and the name of the person on the same row so I can get the correct info on how many minutes the person was on a break (Index 6) or at a certain position. Also I don't know how to take into account combined positions.

 

Any help would be highly appreciated.

10 Replies

  • If you can live with minute level granularity (ie not down to the second) then you can use GENERATESERIES and INTERSECT/EXCEPT approach to collect the number of minutes for each position regardless of they are combined or not.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi,

     

    Thank you for replying. Yes, minute level is accurate enough. I am not very competent with DAX so could you elaborate on how to exactly use those functions in my situation?

     

    I noticed that I probably wasn't as clear as I could have been in my original post so I apologize for that. Here is a simplified table that hopefully clarifies what I am trying to accomplish.

    ā€ƒ

    • ERD's avatar
      ERD
      Community Champion

      Hello Anonymous ,

      There is already a solution similar to your case that can be found here.

      In your case just use DATEDIFF function in RETURN part with MINUTE as interval.

  • One quick question - what datetime locale is that data from?

    Also - do you need this as a measure or is a calculated column enough ( I assume it is)?

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi,

       

      Times are Eastern European Standard Time and a calculated column is exactly what I'm looking for.

      • lbendlin's avatar
        lbendlin
        Super User

        Then the DAX I posted should work. Adjust it to your actual table name.

  • You can AVERAGE()  the duration for each eligible ID to arrive at the working time.