Create table that summarizes employee table into count by month

[StaceyLGriffeth]

I have a table "Employee" which lists all employees, hire date, termination date, etc.  Is there a way to calculate a table from the Employee table which lists each month's end date and a calculated count of active employees for that month, where:

• hire date is <= month end date
• hire date is not blank
• termination date is > month end date OR termination date is blank

I've successfully used a measure to display the data this way on a bar chart by month, but in order to make some other measures and columns work, I need to have the data in the requested structure.

Does anyone have any ideas how to do this?

Thanks!

[amitchandak · Accepted solution]

@StaceyLGriffeth , You can measure in a blog or file attached and Hire date in blank

Power BI: HR Analytics - Employees as on Date: https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...