Active Employees & Retention Rate

[corange]

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.

[cnweke]

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.

[corange]

Hi,

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

[cnweke]

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.

[corange]

Hi,

Yes we should have the or statement. An employee is active if the Finish Date is blank or the date is set in the future. The condition is one or the other.

Is the formula stated before still accurate?

Thanks

[cnweke]

Yeah, just try it out and see if it runs and get back to me.

If it doesn't run, you should check whether or not your EndDate is a Date data type and not just a string.

[corange]

Hi,

It is a date type. I just double checked. This is what I am getting.

[cnweke]

My bad!

 calculate(Count(Employees), filter([TABLE],EndDate > today() || isblank(EndDate) = TRUE))

You need the 'Filter' statement in order to use an or statement in calculate.

[corange]

Hi,

Thank you. This is what I have changed the formula too and still nothing.

[cnweke]

Check if you have the right amount of brackets. Otherwise I don't know.

[corange]

This is what I am getting.

[cnweke]

Yeah I see. It is related to your brackets after all you're doing this:

ISBLANK(DATE = true)

it should be:

ISBLANK(DATE) = true

What you are initially saying is:

Is date = true?

What you should be doing is:

Is a row of the column date blank

[corange]

Thanks so much for double checking this for me .

All working fine.

I will look into the retention rate and get back to you.

[corange]

Hi,

I have looked into the different options and as you advised, I believe a calculated column is better.

However, they are few things I need to take into consideration. My calculated column should not include any results where the finish date is in the past. I also have finish dates set up in the future and I would like to have the retention rate calculated as current not based on the future date. Lastly, some employees have been working for us in a matter of months and I would like it to be simply displayed as months without details but have it definite for years.

Would you be able to guide me ?

thanks

[cnweke]

I've got a busy day today sadly but I can walk you through the concepts:

Concatenatex is your friend here, you can use that to filter a table to your liking and ONLY concatenate those. Try and alter the calculated column to return something of that format. 

[corange]

Hi,

I have checked and the number of brackets seems to be correct. I am not sure why it is not working.