Active employee count for specific year

[Anonymous]

Hello,

I have count of people who are starting and who are terminating year wise. I want the active employee count as per the year if I select year from filter pane. Please find attached image for more information.

[sunny_talwar]

When did EMP ID 6 started? he is getting terminated in 2002?

[Anonymous]

Oops actually i cant share complete dataset due to company issue but i want approach to get the active employee count you can assume he started in 2001 like that.

[sunny_talwar · Accepted solution]

We are not looking for your actual data my friend.... we are looking for a representative data which is close to your actual data. I already provided one solution based on what you shared... I am going to provide another... but if that doesn't work... I am afraid I am not going to attempt again unless you provide a sample which is more inline with your actual data

Count(DISTINCT {<Year = {"$(='<=' & Max(Year))"}, Attribute = {'StartYear'}>}[EMP ID]) -

Count(DISTINCT {<Year = {"$(='<=' & Max(Year))"}, Attribute = {'Terminate Year'}>}[EMP ID])

[Anonymous]

This option is correct but when I select 2002 it shows 3 rather than showing 5 because till 2002 5  employees were active and after 2002 1 left.

[sunny_talwar]

I think it should show 5 because assuming you have another row for EMP ID = 6 for start year... we would count 6 for start year and 1 for terminate year.... 6 - 1 = 5.

[Anonymous]

Got that IT worked perfectly.............!!!!