Forum Discussion
Post PatronActive Employees & Retention Rate
Hi,
I am looking for some assistance being really new to POWER BI. I have a set of data with a start and finish date for all employees. The finish date is either empty for employees still working with the company. The finish date column can also contains a date in the past or in the future if the employee is scheduled to end his employment with the company at a set date.
I would like to calculate the distinct number of employee that are active. This will include all employee whom finish date is empty or set in the future (based on the day we update the report)
I would also need so directions in caculating the retention rate of employee based on their start and finish date. It will also have to considet the finish date set in the future and exclude conting them as still working for the company as of today and adapt result once we go over the set date. I am thinking of a split like 1 year, 4 years etc... without decimal month.
Thanks in advance for your help.
15 Replies
Hey,
This shouldn't be too hard just make a measure with: calculate(Count(Employees),EndDate > today() || isblank(EndDate) = TRUE)
Something along this line should work, just look out for the 'Today()' function.
The second one depends on what you really want to use it for: if you're making a slicer you should honestly make a calculated column, if you just want to know flat out how many there are a measure (per year) should suffice.
Calculated column could be: EmployeeRetention:= Concatenate(concatenate("Active for ",sum(year(EndDate) - year(StartDate))," years(s)")
Play around with it to get it as you please (i.e. changing the year() to month()), do consider that with this code someone that starts in Dec 2018 will be seen as having been active for an entire year in Jan 19.
corangeHi,
This is what I have typed but it doesn't show any results..
ACTIVE EMPLOYEE = CALCULATE(DISTINCTCOUNT(Employee[EmployeeId]), Employee[FinishDate]>TODAY(), ISBLANK(Employee[FinishDate]= TRUE))
With regards to the second part of my question, I will think about it and let you know what will be best.
Thank you
The || (or statement) is crucial. At the moment what you're doing is:
- Show the amount of employees whose end date is further than today
- Show the employees whose end date is blank
If I understand your data correctly this should return 0 people right? No one qualifies to both criteria at the same time.
What you should be doing is (and the or statement should guarantee that) is:
- Is it blank or is the date in the future? Then he's active.
