How to calculate the total employee count for each year based on Hiredate and terminationdate

[Target]

In my data,i have hiredate and termination date columns,based on that i need to arrive the total employee count for each year

[Anonymous · Accepted solution]

Hi @Target ,

Thank you for @bhanu_gautam answer , and I have other suggestions for you:

Below is my Date table(indicate this customer activity time):

Below is my hiredate and termination date table:

You can create a new column to judge the customer whether work in company:

IsActive = 
VAR CurrentDate = 'Date'[Date]
RETURN
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[hiredate] <= CurrentDate &&
(ISBLANK('Table'[termination date]) || 'Table'[termination date] >= CurrentDate)
)
)

And then you can create a measure to calculate the number of the customer:

ActiveEmployees = 
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[hiredate] <= MAX('Date'[Date]) &&
            (ISBLANK('Table'[termination date]) || 'Table'[termination date] >= MIN('Date'[Date]))
        )
    )

The final output will be like this:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Target]

Hi,

 IsActive =
VAR CurrentDate = 'DateTable'[Date]
Here DateTable[Date] is derived from calendarAuto fuction?

[bhanu_gautam]

Yes, it is a date table

[bhanu_gautam]

@Target ,Add calculated columns to determine if an employee was active in a given year. You can do this by creating a column in your date table that checks if the date falls between the hire date and termination date.

IsActive =
VAR CurrentDate = 'DateTable'[Date]
RETURN
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[hiredate] <= CurrentDate &&
(ISBLANK('YourTable'[terminationdate]) || 'YourTable'[terminationdate] >= CurrentDate)
)
)

Than create a measure for active employee

ActiveEmployees =

    CALCULATE(

        COUNTROWS('YourTable'),

        FILTER(

            'YourTable',

            'YourTable'[hiredate] <= MAX('DateTable'[Date]) &&

            (ISBLANK('YourTable'[terminationdate]) || 'YourTable'[terminationdate] >= MIN('DateTable'[Date]))

        )

    )

[Target]

It shows error