Employee Turnover trend calculation

[jhstrange]

Hello,

I am planning to implement Yearly/Monthly employee turnover trend chart for HR.

Employee ID, Status, Hire date and Termination date are the available fields.

The Business user's definition of Turnover = (No of people who were terminated in the last 365 days)/(Count of Active employees)

So, if a person applies a date filter say Jun 29, 2019 , the Turnover calculation should be (count of people who got terminated between Jun 30,2018 to Jun 29, 2019)/(Active employees as of Jun 29,2019)

Kindly refer the image for sample data. Any help would be appreciated.

[thi_pham]

Example Data and result.  Hope it helps

[Data]:
Load * Inline
[
idValue, status, originalHireDate, terminationDate
1,active,1/1/2019,
2,active,1/1/2019,
3,active,1/1/2019,
4,active,1/1/2019,
5,inactive,9/17/2019,9/19/2019
6,inactive,9/5/2019,9/11/2019
];

TurnOver measure = 

sum(if (terminationDate <='$(vFilterDate)', 1, 0))
/
sum(if(originalHireDate <= '$(vFilterDate)' and (terminationDate = '' or terminationDate >= '$(vFilterDate)'), 1, 0))

[jhstrange]

Awesome, thank you!