Creating table of number of active employees per department in different years/months

[DanielB_NL]

Hi all,

I want to make a table to get info about fluctuations in numbers of employees per department, but I can't solve it. I hope someone can help me out.

I have a table with employee data and in this table, among other columns, there are two date columns: 

• in_service_date: the date the employee started at the company (first started at 1995).
• out_service_date: the date the employee left the company:
  • if it has a value in the past, the employee has left the company
  • if it is empty (null value?), the employee is still working at the company
  • if it has a value in the future, the employee will leave the company in upcoming time (for example, when retirement date is known)

Now, I would like to create a table to see the fluctuations in numbers of employees per department in time. So for each year/month/date I need to check whether the employee was working at the company. I think the check has to be something like 'AND(date >= in_service_date, OR(date < out_service_date, out_service_date<>BLANK()))'?!

See the image below for an example of the table I would like to generate. My question is: can someone tell me how to do this? Thanks in advance for your reply!

[amitchandak · Accepted solution]

@DanielB_NL , Refer if my HR blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[DanielB_NL]

Thank you! I've used the 'current employees' formula and it worked fine. Thanks!