Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ramzez
Frequent Visitor

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 10
lbendlin
Super User
Super User

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

lbendlin
Super User
Super 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)?

 

lbendlin_1-1616638338676.png

 

 

Hi,

 

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

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

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".

 

 

 

 

lbendlin
Super User
Super 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

Here is the simplified table:

Excel-file 

ramzez
Frequent Visitor

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.

Screenshot 2021-03-24 at 9.02.37.png

ERD
Community Champion
Community Champion

Hello @ramzez ,

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.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

lbendlin
Super User
Super 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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors