Calculate Rolling Sickness Rate From Cumulative DataSet

To calculate a rolling 12-month sickness percentage with cumulative, duplicated employee data, you must calculate Available FTE on a month-by-month basis, then sum these values over a 12-month rolling period. Using Pivot Tables or Power Query is best, ensuring you sum FTE by Month/Employee ID before calculating the rolling total.
Step-by-Step Approach:
1. Clean and Organize Data: Ensure all tables have a consistent "Month" identifier (e.g., end of month date).
2. Calculate Monthly Available FTE (Solving Duplication):
Do not just sum the FTE column. Because employees are duplicated with different FTEs per month, create a Pivot Table.
Rows: Month, Employee ID
Values: Max of FTE (or Average of FTE if they have multiple entries in one month).
Alternative (Power Query): Group by Month and Employee ID, taking the maximum FTE to ensure one accurate entry per person per month.
3. Sum Total Available FTE (12-Month Roll): Use a SUMIFS formula or a Pivot Table to sum the Monthly FTE figures calculated above for the 12-month period preceding the report date.
4. Calculate Sickness Days: Aggregate total sickness days from the Sickness Table for the same 12-month rolling period.
5. Calculate %:

Note: If sickness is measured in days, convert FTE to days (e.g., FTE x 21.67
average working days per month).

This ensures that if an employee changed from 0.5 FTE in January to 1 FTE in February, those changes are captured correctly by the specific month, rather than double-counted.

I don't think that the employee table is the problem so much as the sickness table - that is potentially split across multiple months. One solution would be to split each absence into separate rows, with one row for each month affected. You could create a calculated table like

Monthly Sickness Table =
SELECTCOLUMNS(
    GENERATE(
        Sickness,
        VAR StartDate = Sickness[Absence Start Date]
        VAR EndDate = Sickness[Absence End Date]
        VAR NumDaysSick = Sickness[Calendar Days Lost]
        VAR FTEDaysSick = Sickness[FTE Lost]
        VAR SickDays = DATESBETWEEN( 'Date'[Date], StartDate, EndDate )
        VAR DaysAndMonth = SELECTCOLUMNS(
            FILTER(
                'Date',
                'Date'[Date] IN SickDays
            ),
            'Date'[Date],
            'Date'[Start Of Month]
        )
        VAR MonthWithDayCount = ADDCOLUMNS( 
            GROUPBY(
                DaysAndMonth,
                'Date'[Start Of Month],
                "@Num days", SUMX( CURRENTGROUP(), 1 )
            ),
            "FTE Sick Days", 
            VAR Ratio = DIVIDE( [@Num days], NumDaysSick )
            VAR Result = Ratio * FTEDaysSick
            RETURN Result
        )
        RETURN MonthWithDayCount
    ),
    Sickness[Employee Number],
    'Date'[Start Of Month],
    [FTE Sick Days]
)

Then make sure that your date table is linked to the new Monthly Sickness table and the employee table and you could write a measure like

Absence % 12 month average =
VAR DatesToUse =
    DATESINPERIOD ( 'Date'[Start of month], TODAY (), -12, MONTH )
VAR DatesWithAbsenceData =
    ADDCOLUMNS (
        DatesToUse,
        "@sickness", CALCULATE ( SUM ( 'Monthly Sickness'[FTE Sick Days] ) ),
        "@fte days available",
            VAR NumWorkingDays =
                CALCULATE ( COUNTROWS ( 'Date' ), KEEPFILTERS ( 'Date'[Is Working Day] = 1 ) )
            VAR FTE =
                CALCULATE ( SUM ( Employees[FTE] ) )
            VAR Result = NumWorkingDays * FTE
            RETURN
                Result
    )
VAR Result =
    AVERAGEX ( DatesWithAbsenceData, DIVIDE ( [@sickness], [@fte days available] ) )
RETURN
    Result