Forum Discussion

AlperenA's avatar
AlperenA
Regular Visitor
2 years ago
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 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. 🙂

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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's avatar
      AlperenA
      Regular Visitor
      2 years ago

      Okey 

      Date Table;

      DateWork Day
      07/01/20241
      07/02/20241
      07/03/20241
      07/04/20241
      07/05/20241
      07/06/20240
      07/07/20240
      07/08/20241
      07/09/20241
      07/10/20241
      07/11/20241
      07/12/20241

       

      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;

       

      NameHire DateTermination Date
      Alperen07/10/2024-
      Kevin06/01/202407/13/2024
      John05/10/2024-
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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