Creating sum function in date table from conditional data in another table

[BJE]

I have an employee dataset that will be updated daily. The dataset contains a single row for each employee and the columns contain a range of information including title, employment basis, commencement date, termination date, standard hours, etc.

Using this data, I need to calculate totals based on certain characteristics for different intervals (i.e. monthly). The calculation needs to work retrospectively as well as apply to additional data going forward.

I have two data tables for this purpose. An example of the employee data is below:

EMPNO	FTE	COMMDATE	TERMDATE
1	1	14-Jul-81	2-Jul-04
2	1	3-Nov-16
3	1	19-Sep-88	22-Aug-07
4	1	1-Nov-18
5	1	1-Nov-18
6	1	1-Nov-18
7	1	1-Nov-18
8	1	1-Nov-18	28-Jun-19
9	1	1-Nov-18	29-May-19

Secondly, I have a dates table as follows:

Year	Month	Month Start Date	Month End Date	Year YTD Start Date	Year YTD End Date
2015-16	Jul-15	1/07/2015	31/07/2015	1/07/2015	31/07/2015
2015-16	Aug-15	1/08/2015	31/08/2015	1/07/2015	31/08/2015
2015-16	Sep-15	1/09/2015	30/09/2015	1/07/2015	30/09/2015
2015-16	Oct-15	1/10/2015	31/10/2015	1/07/2015	31/10/2015
2015-16	Nov-15	1/11/2015	30/11/2015	1/07/2015	30/11/2015
2015-16	Dec-15	1/12/2015	31/12/2015	1/07/2015	31/12/2015
2015-16	Jan-16	1/01/2016	31/01/2016	1/07/2015	31/01/2016
2015-16	Feb-16	1/02/2016	29/02/2016	1/07/2015	29/02/2016
2015-16	Mar-16	1/03/2016	31/03/2016	1/07/2015	31/03/2016
2015-16	Apr-16	1/04/2016	30/04/2016	1/07/2015	30/04/2016
2015-16	May-16	1/05/2016	31/05/2016	1/07/2015	31/05/2016
2015-16	Jun-16	1/06/2016	30/06/2016	1/07/2015	30/06/2016

I need to create a unique sum of FTE hours for all employees that were current for a given period and do this for all periods. The parameters for a given period would be that COMMDATE <= Month End Date and TERMDATE >= Month Start Date or TERMDATE is null.

I think this needs to be done as an additional column to my date table rather than as a measure but whatever approach I have taken has not worked. Any advice from anyone that has encountered this problem and developed a solution is welcome.