Power Bi Desktop staff activities time sheet

[leon_taljaard]

Hi There, 

Please could someone assist. I am not that familiar with Power Bi. I am trying to work out how many hours someone has worked from SharePoint list data into Power Bi Desktop. 

Now I have the data coming in and the number of activities work great but my question is this, if we have staff members who have or log 10 activities for the day but some of those activities are actioned/worked on at the same time. 

So for example below 

Task 1 has a start time of 8am and an end time of 9am but Task 2 and 3 were also started and completed at that same time (Mulittasking) 🙂 So 3 tasks were completed in 1 hour.

How do I calulate that and show that in a card? The reason I am asking is that I have multiple entries/rows with a start and an end time but my calulation is calculating each row/entry and it shows 3 hours instead of 1 actual hour.

Hope this makes sense, below is an example you will see the first time of 8am - 10:30am is the first activity but then they started the second activity at 9:30am - 10am which is still in between the first one making the two 2h30m not 3h

[leon_taljaard]

I think I found the solution!!

So, the one thing that I had that I could use which was similar to the activity ID that needed to be the same for the Saff member, I had the actioned by field which I used instead of the ID field and that seems to have done the trick.

I would not have been able to get to it without @v-xiaotang , thank you!

[leon_taljaard]

Hi @v-xiaotang , 

Thank you so much for the info and your response, it helped a lot but the issue here is that I do have a "Activity ID field" but its not the same for a single activity. So, for example we have Staff 1 who does 5 activities between 08:00 - 09:00 so the ID for the activities would be different, say for example ID 1, ID 2, ID 3, ID 4, ID 5 but in total those 5 activities took 1 hour only or were done in 1 hour but the calulation is still taking it as 5 hours.

See below after creating your measures:

Hope that makes sense I also understood your response correctly 🙂

Thank you again for the support

[v-xiaotang · Accepted solution]

Hi @leon_taljaard 

Thanks for reaching out to us.

Does your data source have an activity id field? If not, you need to increase it, otherwise it is impossible to distinguish different activities with overlapping time

Sample data

create 2 measures, DAX code:

time = DATEDIFF( MIN('Table'[Activity Start Time]),MIN('Table'[Activity End Time]),MINUTE)/60

Measure = 
var _activityID=SELECTEDVALUE('Table'[activity ID])
var _time=MAXX(FILTER(ALL('Table'),'Table'[activity ID]=_activityID),[time])
var _staffname= SELECTEDVALUE('Table'[Staff Name])
return "Staff Name: "&_staffname&UNICHAR(10)&"activity ID: "&_activityID&UNICHAR(10)&"time: "&_time

result

Best Regards,

Community Support Team _Tang

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

[leon_taljaard]

Hi, 

Not sure if you managed to see my reply to this perhaps?