I am analyzing data about employee headcount and I am looking for a way to simplify my formulas.
My end goal is to be able to have measures that give me headcount on a given date, how many employees were gained between the given date and the previous month, how many were lost between the given date and the previous month, etc. If I had a table of employees on a given date, these measures become simple: I can use COUNTROWS to get the current total, EXCEPT to find the gain/loss between the date and the last month, etc.
The problem I have is that my source table is not well set up for that work. In short, we have columns for hire date, termination date, and rehire date. We have people who were terminated in the past, then rehired, and they count as active. We have people who were rehired but then reterminated, so their (new) termination date is greater than the rehire date, and we don't count them as active. Etc.
I can figure out who is active on a given date by filtering the data in a variety of ways, storing each subset of the data to a variable, and UNIONing the variable tables together.
I would like to write that code once and refer back to it in each measure, instead of copying and pasting to each measure. If that code just gave the count of who was active on a given day, I could define it as a measure and call that measure in other measures. But, I need to have the actual table so I can see, e.g., which employee IDs were active on a given day but were not active a month previously, so I get the gain and loss separately (and not just the net change).
Is there a way to do this?
My end goal is to be able to have measures that give me headcount on a given date, how many employees were gained between the given date and the previous month, how many were lost between the given date and the previous month, etc. If I had a table of employees on a given date, these measures become simple: I can use COUNTROWS to get the current total, EXCEPT to find the gain/loss between the date and the last month, etc.
The problem I have is that my source table is not well set up for that work. In short, we have columns for hire date, termination date, and rehire date. We have people who were terminated in the past, then rehired, and they count as active. We have people who were rehired but then reterminated, so their (new) termination date is greater than the rehire date, and we don't count them as active. Etc.
I can figure out who is active on a given date by filtering the data in a variety of ways, storing each subset of the data to a variable, and UNIONing the variable tables together.
I would like to write that code once and refer back to it in each measure, instead of copying and pasting to each measure. If that code just gave the count of who was active on a given day, I could define it as a measure and call that measure in other measures. But, I need to have the actual table so I can see, e.g., which employee IDs were active on a given day but were not active a month previously, so I get the gain and loss separately (and not just the net change).
Is there a way to do this?
