I am attempting to create a Power BI measure that computes the number of active employees and that can be sliced by date, based on a table that is a running list of employment changes.
As a sample data set:
My expected output:
I'm not sure how to create this kind of measure. In plain language, I want to get a table of the most recent one entry for each person (with change dates on or before the date in question), and I want to count how many of those most recent entries have a status active.
As a sample data set:
| Employee ID | Change Date | Status | Title | Change Reason |
| A | 1/1/2021 | Active | Director of Stuff | Hired |
| B | 2/1/2021 | Active | Minion | Hired |
| A | 5/1/2021 | Active | VP of Stuff | Promotion |
| B | 6/1/2021 | Terminated | Minion | Fired |
| C | 8/1/2021 | Active | Minion | Hired |
| B | 9/1/2021 | Active | Minion | Rehired |
My expected output:
| Date | Active Employees | Detail |
| 1/1/2021 | 1 | A |
| 2/1/2021 | 2 | A and B |
| 5/1/2021 | 2 | Still A and B, even though A appeared twice |
| 6/1/2021 | 1 | Just A, since B was terminated |
| 8/1/2021 | 2 | A and C; B is still terminated |
| 9/1/2021 | 3 | A, B, and C; A and B only count once even though they have multiple lines, and even though B was hired and rehired (only the most recent status counts) |
I'm not sure how to create this kind of measure. In plain language, I want to get a table of the most recent one entry for each person (with change dates on or before the date in question), and I want to count how many of those most recent entries have a status active.
