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.
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.