topic Re: How can I track multiple time differences simultaneously based on another column? in DAX Commands and Tips

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

Anonymous — Tue, 16 Mar 2021 09:50:39 GMT

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.

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

lbendlin — Wed, 24 Mar 2021 00:53:30 GMT

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.

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

Anonymous — Wed, 24 Mar 2021 07:08:10 GMT

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.

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

lbendlin — Wed, 24 Mar 2021 13:02:16 GMT

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

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

ERD — Wed, 24 Mar 2021 15:22:57 GMT

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.

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

Anonymous — Wed, 24 Mar 2021 18:40:06 GMT

Here is the simplified table:

Excel-file

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

lbendlin — Thu, 25 Mar 2021 02:12:26 GMT

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)?

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

Anonymous — Fri, 26 Mar 2021 06:01:53 GMT

Hi,

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

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

lbendlin — Fri, 26 Mar 2021 11:46:25 GMT

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

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

Anonymous — Tue, 30 Mar 2021 13:19:45 GMT

It works perfectly! Thank you very much!

I also need to calculate duration when the person is working on any position. Should I make a separate forum post on this?

I updated the sample date. What I need to calculate is the data in the "Working time" column.

Excel

Each entry has its own ID number ("ID column") so this can be used and when positions are combined, they get the same ID number. I need to calculate everything else except "OFFWORK", "BREAK" and "OFFICE".

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

lbendlin — Tue, 30 Mar 2021 17:35:08 GMT

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