Forum Discussion

leon_taljaard's avatar
leon_taljaard
Frequent Visitor
4 years ago
Solved

Power Bi Desktop staff activities time sheet

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

 

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

4 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      leon_taljaard
      Frequent Visitor
      4 years ago

      Hi, 

       

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

  • leon_taljaard's avatar
    leon_taljaard
    Frequent Visitor
    4 years ago

    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

  • leon_taljaard's avatar
    leon_taljaard
    Frequent Visitor
    4 years ago

    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!