Using Power BI Desktop I am trying to create a report that will allow us to visualize and track how much time staff are spending on various tasks through the day.
Because they switch from task to taks, there is no specific StartTime or StopTime, just a timestamp and a note about what task was started.
Multiple instances of the same task could be triggered due to current limitations in our system. Sometimes we have to take the earliest instance as "the" timestamp, and sometimes we have to take the latest instance as "the" timestamp.
An example of one user's raw data would look something like this:
The key items are in the Action column:
"Previewed Job List" - starts their day
"Previewed MTO Logbook" - completed morning shop time, ready to roll
"Job Started" - arrived at jobsite (1 or more times per day)
"*COMPLETE Job Status" - finished at the jobsite (1 or more times per day)
"ts07" - Arrived back at shop
"ts08" - Logged out. Completed evening shop time.
Figuring out the difference between "Previewed Job List" and "Previewed MTO Logbook" will give their morning shop time.
Figuring out the difference between "ts07" and "ts08" will give their evening shop time. And totalling those two differences will give the total shop time for the day.
Figuring out the difference between "Previewed MTO Logbook" or "*COMPLETE job Status" and "Job Started" will give travel time to each job. Totalling all those will give total travel time.
Figuring out the difference between "Previewed Job List" and "ts08" will give total hours for the working day.
Some crews work overnight, so crossing midnight can happen.
I tried following along with another forum message that was similar (Calculate On-time versus Off-time), but was having trouble following along with the actual working result.
Is there a different post I could review that might point me in the right direction more? Or a good tutorial? I'm almost thinking I need to go back to the MySQL query and try to re-jig things there to create a view or something.
I then created a measure to calculate the difference, and ran into issues. Trying to use DATEDIFF and kept getting the error that the one number had to be bigger than the other number. Until I found this forum thread:
which I was able to modify and then view my data in a matrix.
What I noticed was that if there were two "Start Activity" entries on the same day for the same employee, the difference calculation could end up being a negative number as the "Start Activity" could end up being completed *after* the "Stop Activity"
Looking closer at the measure you provided, I reviewed my "StartShop" measure and changed it to:
which now appears to find the *first* "Start Acitivty", and thus getting rid of my negative shop times.
So now it appears I just need to create more measures, as you suggested, to calculate all the different times I need, and then summarize them (Total Shop Time Per Employee Per Day, Total Travel Time Per Employee Per Day, Total Working Time Per Employee Per Day, Total Clocked Hours Per Employee Per Day, etc.)
Thanks for the pointer. I'll finish this part up and report back.