Forum Discussion
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_difThis 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
- lbendlinSuper User
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.
- AnonymousNot 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.
ā
- lbendlinSuper User
Please provide the sample data in usable form (not as a picture) and I can show you the basic steps.
Here is a similar topic where I have offered a potential solution
Calculate Time Occupied Over Call during an Hour - Microsoft Power BI Community
- AnonymousNot applicable
Here is the simplified table:
- lbendlinSuper User
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)?
- AnonymousNot applicable
Hi,
Times are Eastern European Standard Time and a calculated column is exactly what I'm looking for.
- lbendlinSuper User
Then the DAX I posted should work. Adjust it to your actual table name.
- lbendlinSuper User
You can AVERAGE() the duration for each eligible ID to arrive at the working time.