Forum Discussion
Active 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.
Hi Sascha,
I like your apporach. I am new to Power BI - and am looking to sort Joiners, Leavers and Active Running total by month, quarter and YTD. Our fiscal year is July-June. Appreciate your help here.
Thanks.
I 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.
I see that you do take the acocunt for termintation dates in the future, correct ?
Im asking cause it seem that DAX calculation is exactly excluding all of current active employees with a termination date in the future, and cant figure out why that is.
Anonymous - My DAX formula calculates the number of active employees at the end of any given period. The initial Max date variables captures the max date given the current date context. Then, the CalculateTable function creates a table of all employees hired on or before the max date. The ALL function causes the CalculateTable to ignore the current date context. I'm doing this because the date context has a min and max date and I need to not only find all employees within the date context but also those hired before the date context; since those may still be active. The Calculate function looks for all active employees found in the intermediate table. Active employees are defined as those whose termination date is either blank or is greater than the max date of the period in context.
Hope this helps.
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() ) ))But if an employee has 2 sets of hire date on the same year, it seems to get counted as dobbel.ex.The last employee should only be counted as 1 if year is filtered to 2015.Can I somehow take that into account ?Any help/advise would be appreciated.Thanks.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.