Jump to content
We've recently updated our Privacy Statement, available here. ×

Calculating Rolling Turnover using two different date columns


Corporate Analytics

Recommended Posts

I have appended Datafiles that I need to use to calculate Rolling 12 month Turnover %.(the attached sheet does not date back 1 year, but shows all of the other columns that I must be able to filter or limit by)

Where:

Turnover %= Total Terminations/((Headcount 1+ Headcount 2)/2)

Because the files have been corrected over time, I must Count [Termination Date]for the most recent month

Terminations= COUNT([Termination Date])

-Limited by the expression: Month([Month]) = month(DateTimeNow()) and Year([Month) = year(DateTimeNow())

I obtain Headcount by counting active employees for each Month.

Headcount= COUNT([Employee Status])

-Limited by the expression :[Employee Status]="Active"

 

How can I create a table that:

(1) is organized by Month,

(2) looks up the Headcount from 12 months ago

(3) totalsthe current month headcount

(4) totals the terminations from the last 12 months, based on the current month data.

 

 

 

Month

Headcount 1 year ago

Current Headcount

Terminations total for last year

12 month Turnover %

 

 

4/1/2019

 

 

 

 

 

 

5/1/2019

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...