Employee Turnover with Date Hierarchy

[brethomp]

I have employee data organized by week, similar to below. So for each week that an employee has been with the company, they will have a line item in the data for that week with thier status in that week, and several other columns of infomation. All of these column values change over time to keep a history.

I would like to do a Turnover report from this data, that will be able to tell me how many people were hired or terminated in a specified time period, as well as how many were active for that time period. This works great if I do this for each week. But when I roll it up to a month or year, employees are double counted if there are multiple values for any of the columns like Status, Office, or Department from different weeks in that time period.

Focusing on the status column, if in one of the weeks of the month the status column contains "Hired" I need that employee to show as "Hired" in that month, quarter or year. Same with Terminated. And I also need to plan for the special case where an employee was both Hired and Terminated in the same month, quarter, or year.

Using this sample data in the image below, by week is fine, but the graphic below rolled up to the month double counts many of the employees. This is with the Count of Employee Number set to Distinct. And it works fine when I remove the Status from the legend. But I do want to see which portion of the total number is new hires and terminations. And eventually incorporate other data to try to get some insights on who is leaving and why.

I think I need to do some sort of Summary table with DAX code, but I can't figure out how to do the calculation to show just hired of terminated when rolled up.

[brethomp]

Apparently I have stumped the experts. DAX seems pretty powerful, I am surprised that no one has a solution for this. Anyone else have an idea?

[ChandeepChhabra]

@brethomp Hi

Create a calendar table and esatblish relationship with Emp Table

Then try these 3 measures

1. Hired = CALCULATE(DISTINCTCOUNT(Emp[Emp ID]),Emp[Status]="Hired")

2. Terminated = CALCULATE(DISTINCTCOUNT(Emp[Department]),Emp[Status]="Terminated")

3. Active Emp = CALCULATE(DISTINCTCOUNT(Emp[Emp ID]),Emp[Status]="Active")

You can download this PBIX file

Hope this helps

[brethomp]

@ChandeepChhabra, thank you for the reply, but unfortunately that example still has the same issue. The raw data only has 5 employees total. When rolled up to a month, the employees that are both hired and active in the month are counted twice, in 2 different columns. I want the total number of employees in the by month (quarter or year) columns to total 5, and then color by how many were hired or terminated in that month.

Maybe the approach in this solution is in this "Active" measure, filter out any employee numbers that are already included in the Hired or Terminated column. But I don't know how to do this in DAX.

[ChandeepChhabra]

@brethomp 

1. I am not sure if a stacked visual is possible if you are trying to show a total of 5 emp and then split by terminated and hired employees
2. To get the total employees by month, you can revise the active employee measure = DISTINCTCOUNT(Emp[Emp ID])

If I din't answer your question adequately, could you please share your data and snapshot of the output that you are expecting

Thanks

[brethomp]

@ChandeepChhabra, thank you for trying to help, but I am not ready to give up yet. DAX seems like a powerful tool, there could be a way to accomplish this. Maybe somone else has an idea. I know it is a piece of cake in SQL, but doing it there I would need to bring in 4 different views of the data into Power BI for Year, Quarter, Month, and Week, and not use the drill down tools in Power BI.