Calculate Employee Capacity

[AlperenA]

Hello,

I need to calculate employee capacity. For this, I have a calendar table and an employee table. My employee table includes hire and termination dates.

In my calendar table, I have marked working days with a coefficient of 1 and other days with 0. To calculate the working days for employees, I need to account for the days between the hire date and the termination date.

When I use Sumx, both dates are not coming through within the filter.

I would appreciate your assistance on this matter. 🙂

[rajendraongole1]

Hi @AlperenA - can you please provide sample data and table details for more understanding.

Thank you

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[AlperenA]

Okey 

Date Table;

Date	Work Day
07/01/2024	1
07/02/2024	1
07/03/2024	1
07/04/2024	1
07/05/2024	1
07/06/2024	0
07/07/2024	0
07/08/2024	1
07/09/2024	1
07/10/2024	1
07/11/2024	1
07/12/2024	1

I am summing the working days, but I need to sum the days after the employee's hire date and the days before the termination date.

Employee Table;

Name	Hire Date	Termination Date
Alperen	07/10/2024	-
Kevin	06/01/2024	07/13/2024
John	05/10/2024	-

[Anonymous · Accepted solution]

Hi @AlperenA ,

You can create a calculated column in Employee table as below to get it, please find the details in the attachment.

Work Days = 
CALCULATE (
    COUNT ( 'Date'[Date] ),
    FILTER (
        'Date',
        'Date'[Work Day] = 1
            && 'Employee'[Hire Date] <= 'Date'[Date]
            && IF (
                ISBLANK ( 'Employee'[Termination Date] ),
                TODAY (),
                'Employee'[Termination Date]
            ) >= 'Date'[Date]
    )
)

Best Regards

[AlperenA]

Thank you 💪