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 w...
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

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

      • AlperenA's avatar
        AlperenA
        Regular Visitor
        2 years ago

        Thank you ðŸ’ª