Forum Discussion

Landcrab's avatar
Landcrab
Helper I
8 years ago

Visualise workload by consultant

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 con...
Landcrab's avatar
Landcrab
Helper I
8 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Landcrab
      Helper I
      8 years ago

      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's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        Share some data and show the expected result.