Visualise workload by consultant

[Landcrab]

Hi there,

Im new to Power BI and hope someone can give me a hand please.

I want to create a report that shows the Available Hours, Booked Hours and Booked % (Booked Hrs / Available Hrs) for consultants, summarized by Month with drill down to week, so that every month has weeks numbered 1, 2, 3, 4 with 30 Available Hours per week that can be allocated to work (Booked Hours) as required.
A week has 5 working days (Mon-Friday) with 6 work hours per day.

There is Date table and another table that captures Consultant Name, Start Date, End Date and Hours the job will take.

Example:

Start Date: 1 Aug 2018
End Date: 6 Aug 2018
Hours: 12

Based on the Start Date (possibly the End Date (if required)) and the Hours for the job, I need to record the Booked hours against a week and be able to view that by Month and drill down to week, as per Excel picture.

It is possible that 2 or more jobs fall with in the same date range, in which case the consultant is overbooked (Booked> 30 and Booked % > 100%) and will show as Booked % > 100%.

Any ideas are much appreciated.

[Landcrab]

Forgot to say, although I have a Start Date, End Date and Hours, the reason the Start Date and Hours is the way I want to record it is because the End Date might not be firm. That is, in some cases work will be booked like this:

Start Date: 1 Aug 2018

End Date: 31 Aug 2018

Hours: 8

Which means, the is 8 hours of work to be done in August but they could fall in any of the 4 weeks of August, hence the Start Date determines which week to subtract the Booked Hours from and the Hours determine over how many days (weeks) the work stretches and allocates to.

[v-huizhn-msft]

Hi @Landcrab,

In the screenshot posted in previous thread, you get the percentage using excel formula, you can transfer it to DAX in Power BI desktop. You can share the excel formula and we help you in details.

Best Regards,
Angelia

[Landcrab]

Hi @v-huizhn-msft,


Thank you for the response. My source is actually a SharePoint list where users enter the Start Date, End Date, Hours, the Excel screenshot was just to show the end result I want to achieve in the Power BI report.

The Start Date determines which Calendar week the Hours get allocated to and summed up for.

Example:

Job 1
Consultant: p1
Start Date:  1 Aug 2018
Hours:    12

Job 2
Consultant: p1
Start Date:  3 Aug 2018
Hours:    6
 
Job 3
Consultant: p1
Start Date:  11 Aug 2018
Hours:    8

Job 3
Consultant: p2
Start Date:  11 Aug 2018
Hours:    3


The above then gets summarized by Consultant by week as follows:

Consultant:            p1
August week 1 Hours:   18 (12 +6)


Consultant:            p1
August week 2 Hours:   8

-----------------------------------------

Consultant:            p2
August week 1 Hours:   0


Consultant:            p2
August week 2 Hours:   3



Once I have the measure summing the total hours per week per consultant I can then put that in a Power BI table to see week by week how busy Consultants are, i.e. are they over 100% allocated then we need more resources to do the job.


Attached picture is my attempt at deriving that measure with the given data I have, but obviously that formula is not right so far 😞


Any ideas how I might get the desired result using a measure please?

Thanks,
Nick

[Ashish_Mathur]

Hi,

Share some data and show the expected result.