Forum Discussion
Solved
Calculate Employee Capacity
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 w...
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
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 | - |
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
Thank you 💪