Forum Discussion
Responsive ResidentActive Employees per Period
Thank you for the advice. I tried it and do not get the correct result, either. What I do get is the number of employees, who left in a specific period (e.g. month).
As I have to deliver the report today, I resolved to take a different approach, showing the number of joiners, leavers and overall evolution of active personnel as in the chart below.
Helper III encountered this same requirement this week. Here is how I solved it.
# Employees At End of Period =
VAR MaxDate = MAX ( 'Date'[Date] )
VAR EmpCnt =
CALCULATE (
COUNTROWS (
CALCULATETABLE ( 'Employees', 'Employees'[HireDate] <= MaxDate, ALL ( 'Date' ) )
),
(ISBLANK ( 'Employees'[TerminationDate] ) || 'Employees'[TerminationDate] > MaxDate)
)
RETURN
IF ( ISBLANK ( EmpCnt ), 0, EmpCnt )
In my data set, employees that are currently active have a blank termination date. The Calculate function returns Blank, instead of zero, when the count is zero for any given period; which is why the last IF is used after the RETURN.
Hi rl_evans
So I ended up with this DAX calculation with some help,
Count of active employees =
VAR EndOfPeriod = MAX ('Calendar'[Date])
VAR StartOfPeriod = MIN ('Calendar'[Date])
RETURN
CALCULATE (
DISTINCTCOUNT(v_FSASHRBIDATA[EmpId]),
FILTER(
ALL('v_FSASHRBIDATA'),
(v_FSASHRBIDATA[EmploymentDate] <= EndOfPeriod &&
v_FSASHRBIDATA[TerminationDate] >= StartOfPeriod)
)
)
+
CALCULATE(
DISTINCTCOUNT('v_FSASHRBIDATA'[EmpId]),
FILTER(
ALL(v_FSASHRBIDATA),
('v_FSASHRBIDATA'[EmploymentDate]<=EndOfPeriod
&& 'v_FSASHRBIDATA'[TerminationDate] = blank()
)
))
- rl_evans
Anonymous I guess it depends on what you're trying to count. If the same person is hired twice in the same year, isn't that the same as hiring two people that year? Just because it's the same person doesn't change the fact that there were two hirings. In other words, if you ignore the name of the employee, the events are 1) and employee is hired, 2) an employee is separated, 3) an employee is hired. That's two hires and one separation that occurred in the year. That doesn't change the number of employees that were active in any given period.
rl_evans I agreed with you, but im also not trying to show how many persons where hired at any given period, but how many where active, and if you have the same person hired twice in a year, in terms of active employees it should only be counted as one, right ? it seems to change the number of active employees on the years basis alot, on month basis it looks better.
- rl_evans
Anonymous Looking at your DAX more closely, one thing that jumps out is that your filter is incorrect:
(v_FSASHRBIDATA[EmploymentDate] <= EndOfPeriod && v_FSASHRBIDATA[TerminationDate] >= StartOfPeriod)
It should look like this:
(v_FSASHRBIDATA[EmploymentDate] <= EndOfPeriod &&
v_FSASHRBIDATA[TerminationDate] > EndOfPeriod)To count all active employees in any given period, you want to include those whose termination date is after the end of the period.
Btw - A year is also a period, so if the same person was hired twice in that period it's irrelevant. If that period is still active at the end of the year, there should be only one record showing that individual as active. So, they'd only be counted once. If your count is off, it's due to a different issue with your DAX.
Finally, in your data, there is a termination date showing the year 1753. This will not work with your DAX statement as this means the person was terminated before they were hired. If that date is being used to indicate the individual is still active, then you need to modify your DAX to look for that specific date (1/1/1753).