Calculating time spent on various activities during the day by multiple people

[addaline]

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.

Suggestions gratefully appreciated.

Thanks in advance.

Dion

[v-sihou-msft]

@addaline

In this scenario, I think you need to add a "work start day" column in your table so that you can group each calculation on "work start day" and user level.

Work Start Date =
IF (
    Table1[Action] = "Work Start",
    Table1[LogTime].[Date],
    CALCULATE (
        MAX ( Table1[LogTime].[Date] ),
        FILTER (
            Table1,
            Table1[Action] = "Work Start"
                && Table1[LogTime] < EARLIER ( Table1[LogTime] )
                && Table1[UserId] = EARLIER ( Table1[UserId] )
        )
    )
)

When this user work overnight, the logged labor will still be considered in previous day.

Then you can filter the table context based on Action to get the corresponding date for calculating time difference.

Measure =
CALCULATE (
    MAX ( Table[LogTimeStamp] ),
    FILTER ( Table, Table[Action] = "XXXX" ),
    ALLEXCEPT ( Table, Table[Work Start Date], Table[UserId] )
)

To calculate difference, just have corresponding measures substracted to get the result.

Regards,

[addaline]

@v-sihou-msft

Sorry for the delay in responding. I haven't had time to sit with this.

I figured out that "Work Start Date" was a new column to add from the reporting screen, not the Edit Queries screen (Yay!) and that seemed to go well.

WorkStartDate = IF (
    ProductionLogs[Action] = "Start Activity",
    ProductionLogs[LogTimestamp].[Date],
    CALCULATE (
        MAX ( ProductionLogs[LogTimestamp].[Date] ),
        FILTER (
            ProductionLogs,
            ProductionLogs[Action] = "Start Activity"
                && ProductionLogs[LogTimestamp] < EARLIER ( ProductionLogs[LogTimestamp] )
                && ProductionLogs[UserId] = EARLIER ( ProductionLogs[UserId] )
        )
    )
)

Then I added two measures based on your suggestion, one called StartShop:

StartShop = 
CALCULATE (
    MAX ( ProductionLogs[LogTimestamp] ),
    FILTER ( ProductionLogs, ProductionLogs[Action] = "Start Activity" ),
    ALLEXCEPT ( ProductionLogs, ProductionLogs[WorkStartDate], ProductionLogs[UserId] )
)

and one called StopShop:

StopShop = 
CALCULATE (
    MAX ( ProductionLogs[LogTimestamp] ),
    FILTER ( ProductionLogs, ProductionLogs[Action] = "Stop Activity" ),
    ALLEXCEPT ( ProductionLogs, ProductionLogs[WorkStartDate], ProductionLogs[UserId] )
)

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:

http://community.powerbi.com/t5/Desktop/Calculate-difference-between-two-date-time-values/m-p/71841/...

and this measure:

Diff = IF(Table1[Column1]<Table1[Column2],DATEDIFF(Table1[Column1],Table1[Column2],DAY),(-1)*DATEDIFF(Table1[Column2],Table1[Column1],DAY))

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:

StartShop = 
CALCULATE (
    MIN ( ProductionLogs[LogTimestamp] ),
    FILTER ( ProductionLogs, ProductionLogs[Action] = "Start Activity" ),
    ALLEXCEPT ( ProductionLogs, ProductionLogs[WorkStartDate], ProductionLogs[UserId] )
)

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.

Dion