Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
You can AVERAGE() the duration for each eligible ID to arrive at the working time.
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)?
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.
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".
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
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.
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!
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.
User | Count |
---|---|
20 | |
18 | |
15 | |
11 | |
7 |
User | Count |
---|---|
28 | |
27 | |
13 | |
12 | |
12 |