Measure Employee Productivity to FTE removing annual and sick leave.

[cjbaguley]

Hey Folks,

I'm working on a productivity dashboard for my team that accounts for annual or sick leave being taken out of the measure.

eg. Employees capture their own productivity and leave through a power app that writes to seperate sharepoint lists and then is picked up by Power BI. All of that side I can get fine, including the measure of how much productivity they have for a given day. 

I'm trying to compare that against a days productive hours of 7.6 which Im also able to get.

Where I'm trying to go though is that if an employee has a partial or full day leave on a given day, it removes an equvalent number of hours from that employees contribution, so if they had 4 hours leave it would reduce that day to 3.6 hours.

The main issue I seem to be running into is if the leave is over a span of days, it bulks up the hours of that leave into a single entry on the first day of leave, rather that counting 7.6 x however many work days they are on leave for. 

I guess the measure or calculated column I'm trying to get to is If an employee is noted as being on leave on a particular date, then it counts the hours noted and subtracts them.

I know I'm explaining this horribly, so I'm hoping this is enough to get started.

[cjbaguley · Accepted solution]

I was actually able to self-solve this with a combination of a few solutions found here.

Firstly, in Power Query I changed the leave start and end dates to integers, and then created a new calculated column using:

Thats = {[Leave Start Date]..[Leave End Date]}

I then expanded that column to split out all the repeat integers, and then converted back to a date format.

Then in data modelling, I linked that date to the master date table of my dataset, and then finally, in table view I added a column in my master date table 

CALCULATE( SUMX ('Coach Leave', 'Coach Leave'[Useable Leave Hours])) that calculates only the leave hours for that particular date.

Seems to be working like a charm, hopefully this helps someone else some day!

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...