Measuring capacity of resources within a payroll year considering the budgeted allocated resources

[3Jk33f3]

I have attempted for weeks to create a measure that would help me figure out what a division's work capacity has been throughout the year so that we can measure impact on productivity due to position vacancies and time off taken. 

I currently have payroll data for the year that contains all paycodes, payroll period, date, etc. See below..

I also have the number of positions we are budgeted for by division: 

For example:

• Division A is budgeted for 12 positions and they experienced some turnover throughout the year.
• At times a position or two would be vacant for a month or two.
• I'd like to have a measure that would calculate the time all people assigned to that division worked.
• I've tried using the number of payroll periods they worked to determine the % of year worked.
• But I get stuck trying to aggregate that number and comparing it to the budgeted positions to determine the division's true capacity for the year.

Measures I've created:

% year worked 

%yearworked = CALCULATE(DIVIDE('Payroll20-23'[PayPeriodsWorked], 26))

Capacity of year worked

CapacityYearWorked = DIVIDE('Payroll20-23'[%yearworked], [FullCapacity])

Pay periods worked

PayPeriodsWorked = MAX('Payroll20-23'[Payroll Period])

Payroll Period

Payroll Period = SWITCH([WeekNumber], 
    1, "1",
    2, "1",
    3,"2",
    4, "2",
    5,"3",
    6,"3",
    7, "4",
    8, "4",
    9, "5",
    10, "5",
    11, "6",
    12, "6",
    13, "7",
    14, "7",
    15, "8",
    16, "8",
    17, "9",
    18, "9",
    19, "10",
    20, "10",
    21, "11",
    22, "11",
    23, "12",
    24, "12",
    25, "13",
    26, "13",
    27, "14",
    28, "14",
    29, "15",
    30, "15",
    31, "16",
    32, "16",
    33, "17",
    34, "17",
    35, "18",
    36, "18",
    37, "19",
    38, "19",
    39, "20",
    40, "20",
    41, "21",
    42, "21",
    43, "22",
    44, "22",
    45, "23",
    46, "23",
    47, "24",
    48, "24",
    49, "25",
50, "25",
51, "26",
52,"26",
 53,"26")

I need to provide this analysis to my leadership team in the next few weeks so any assistance is greatly appreciated. 

Thank you,

Linda 

[lucadelicio]

Hi Linda,

can you share the pbix or the excel sample data to help you?

Thank you!

[3Jk33f3]

Ok, I'm going to try the best that I can to provide sample data. It's payroll data so there's sensitive information, not to mention, so much of it. I made a small data set and hopefully it'll help.  

I can't attach an Excel file so here is my sample data that would export from our payroll system:

Employee ID	Name	Last Hire Date	Divison	Status	Hour Type	Date	Hours	Paycode
12345	Tim	9/26/2016	Team A	Active	1	12/31/2019	14.5	Regular
54321	Alan	9/26/2018	Team B	Active	1	12/31/2019	33.5	Regular
67890	Jodi	9/26/2016	Team C	Active	1	01/15/2020	5	Regular
9876	Alec	9/26/2016	Team D	Active	1	01/15/2020	66	Regular
24680	Ellen	9/26/2016	Team E	Active	1	01/31/2020	1	Regular
12345	Tim	9/26/2016	Team A	Active	1	01/31/2020	9	Regular
97449	Jake	1/15/2020	Team B	Active	1	01/31/2020	69	Regular
67890	Jodi	9/26/2016	Team C	Active	1	02/15/2020	1	Regular
9876	Alec	9/26/2016	Team D	Active	1	02/15/2020	1.5	Regular
24680	Ellen	9/26/2016	Team E	Active	1	02/15/2020	1.5	Regular
12345	Tim	9/26/2016	Team A	Active	1	02/15/2020	9.5	Regular
54321	Alan	9/26/2016	Team B	Active	1	02/15/2020	58.5	Regular
67890	Jodi	9/26/2016	Team C	Active	1	02/29/2020	7	Regular
98760	Alec	9/26/2016	Team D	Active	1	02/29/2020	8	Regular
81435	Tracy	2/18/2020	Team E	Active	1	02/29/2020	57	Regular

This is the "head count" allocated to each division:

Division	Number of position IDs	Year
Team A	18	2021
Team B	9	2021
Team C	11	2021
Team D	11	2021
Team E	7	2021
Team A	18	2022
Team B	9	2022
Team C	11	2022
Team D	11	2022
Team E	7	2022