Calculating Rolling Turnover using two different date columns

[Corporate Analytics]

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