Compare Available Hours to Actual Hours Worked

[gbraunwoodbury]

Trying to calculate total available hours for a team that include both full- and part-time employees.  I want to build a measure that compares available hours to actual hours worked. 

My employee table includes [Name] and [Standard Hours] for each person  It is related to the separate "Time" table (which includes work hours and tasks) through the [Name] field.

I have also created a "Working Days Calendar" that excludes weekends and holidays. It is related to the "Time" table through the [Date] field.

Using it I've built a formula that calculates the number of available workdays.

Days Available = DISTINCTCOUNT('Working Days Calendar'[Working Date])

Then I created another measure for available hours:

Available Hours = 'Working Days Calendar'[# Workdays]*'Team'[Standard Hours]

This gives me the year-to-date standard annual hours for each team member. It works if you assume that everyone was available every working day.  BUT -- team members have joined at different dates throughout the year. I want the measure to show only available hours for each employee starting with the date they began working for us.

How should I handle this? Add a Start Date to the "Team" table and build that date into the measure?  Or somehow relate the measure to the "Time" table, which shows the actual dates worked?

Or maybe there's a more elegant way to do this?

[v-luwang-msft]

Hi @gbraunwoodbury ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[gbraunwoodbury · Accepted solution]

Here's the solution that eventually worked for me:

Billable Hours = CALCULATE(SUM(Harvest[Hours]),(Harvest[Billable?] = "Yes"))

Non-Billable Hours = CALCULATE(SUM(Harvest[Hours]),(Harvest[Billable?] = "No"))

Capacity Hours = SUM('Team'[Standard Hours]) * [Days Worked]

[gbraunwoodbury]

Thank you so much for your suggestion, Ashish -- however in your version the capacity hours are not affected by the date slicers.  When I select Year = 2021 and Month = September on the slicers, I expect to see only the capacity hours for that month, by person.  Instead, I see a constant number for each person regardless of how the slicers are configured.

Report with slicer for 2020:

Report with slicer for Q1 2021 -- same number of capacity hours showing:

[gbraunwoodbury]

Unfortunately when I do that, capacity shows as null.

[Ashish_Mathur]

It does show results for 2021.  Ensure that the working day table is complete.

[gbraunwoodbury]

I must be doing something wrong.  Below is what I see with 2021 selected on the slicer.  I did not change the working day table from what you sent me... how do I ensure it is complete?

[I GREATLY appreciate your help with this, by the way. You are a true community person!]

[Ashish_Mathur]

You are welcome.  Select 2020 (not 2021).  There is no data for 2021 in the Working Days calendar table.

[gbraunwoodbury]

Same result, unfortunately:

[Ashish_Mathur]

I am not sure of how else i can help you.  I have clearly shown you that my method works in the file that i shared with you.

[gbraunwoodbury]

I'm guessing that my idea of using a separate workdays calendar is not going to work for this calculation?

[Ashish_Mathur]

Hi,

Capacity is both a calculated column formula (the one that you created) and a measure (the one that i have creaed).  To your visual, drag capacity (the measure from the Harvest table) not the capacity (the calculated column formula from the Team table). 

[gbraunwoodbury]

Link to the PowerBI files: 

https://www.dropbox.com/sh/zm2ulo88n77dx66/AAA_0s-S03KuvKEhGMe_Xxrua?dl=0

Below is the viz I am trying to create.  The yellow line shows capacity based on a measure of [#work days] * [standard hours].  However the capacity is showing as a constant number of hours for the entire year. I want it to be filtered based on calendar date.  I should be able to compute, for any period, the number of available hours for each person AND the number of hours actually worked.

[Ashish_Mathur]

Hi,

FIrst of all, the Calendar Table should have all dates including weekends and holidays.  Lastly, share some data, describe the question and show the expected result.

[Ashish_Mathur]

Hi,

Download the PBI file from here.

Hope this helps.