If possible, I would do this on the database. Perhaps creating a new view.
If not, you could try a self join via custom SQL. Note this is not in tableau Custom SQL syntax and hasn't been tested, but it should give you an idea. This assumes there is an EmployeeID that is unique each month (no duplicate IDs for in any given month).
First, create a result set that has the employee status for last month and for the current month on the same record.
SELECT coalesce(c.EmpID,p.EmpID) as EmpID,
coalesce(c.EmpName,p.EmpName) as EmpName,
p.Dept as prev_dept,
p.Status as prev_status,
c.Dept as curr_dept,
c.Status as curr_status
FROM employee_table p FULL OUTER JOIN employee_table c
ON p.Month = <last_month>
AND c.Month = <this_month>
AND p.EmpID = c.EmpID ;
Then you can apply logic in Tableau via calculated fiends to decode the data. For example:
IF isnull(prev_status) AND curr_status = "Active" THEN "NEW HIRE"
ELSEIF prev_status = "Active" and curr_status = "Term" THEN 'TERMINATED"
ELSEIF prev_status = "Active" and curr_status = "Active" THEN 'CONTINUING"
You will have to see all the possible cases and add logic to handle them.
Does this make sense?
Pedro gave a good solution using custom SQL, but I wanted to add that it is also just possible in your current data set using a table calculation. I've given the statuses a number so you can see the difference compared to previous month (using the "difference" table calc). I added a bit to your data set to give me more to play with, but I assume that you'll just build in a filter/selection of some sort to pick the months.
See how that works for you.
Track Hires and Fires.zip 17.9 KB