Some other things to consider as you rework your model:
- your two tables with employee information contain a mix of fact and dimension elements. I would first create an employee dimension table, with the static and semi-static/slow changing elements (employee ID, name, department, job title, company, etc.)
- I would look at merging and simplifying the remaining information into a single fact table, and potentially going with a “long and narrow” structure (lots of rows, relatively few columns) that is focused around employee ID (linking back to your employee dimension table above), then HR action type (e.g., hire, terminate, rehire, etc.), status and HR action date. This will allow you to shape your model into a classic star schema, which is optimal for Power BI and DAX.
- from this structure, it should be relatively simple to calculate the most current status for each employee, and then to run counts over any period of the number of employees in active status (i.e., hired or rehired within that period). I’m confident that when you build your model this way the DAX will simplify dramatically (you may actually not need to do much DAX at all, since finding the most current status for each employee given the modified data model will be easy to do in Power Query).
Here are a link w/ a few other recent threads embedded where transformation into that “narrow and long” structure made the calculations much easier.
- Brian