I came across a tricky issue in Power BI that I don't know how to solve.
I am creating a Headcount dashboard that includes a historic headcount graph and table. Time ago I created a dynamic headcount based on start and termination dates that is working fine, month by month. I added that into a table with the months , and then filtered that table by project and type of contract, (for simplicity I will talk just about type of contract) so I got something like this:
Jan22 Feb 22 Mar 22
FTC 2 4 3
Perm 8 8 9
Total Headcount 10 12 12
The issue is that the total headcount is right, but the count for FTC's and Permanent (FTE) is not because that's not taking into account people that went from FTC to FTE
In my dataset I have a column called FTC-FTE that states the date of someone changing type of contract, so we have 3 types of employees
Permanent from the beginning (so FTC-FTE cell is empty)
Employees with a date of change (past or future, FTC if future, Perm if past)
FTC without a date of change (so FTC-FTE cell is empty and they have a termination date)
I got to work out a measure that recognizes the type of contract depending on whether there is or there isn't an FTC-FTE date, and if that's on the future or the past... but I can't get it to work and also don't know how to integrate that into the headcount