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.
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.
Helper IIAnonymous 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).
rl_evans Just to clear it up, the 1753 date gets converted in SQL to blanks, for some reason Navision data directely in the database states it like that, apologise for me lazyness :)
The reason I suspected that, is because no matter what I try, my yearly stats gets messed up, and I cant figure out why.
Changing what you suggested unforntantly sill gave the incorrect results.