Forum Discussion

bigrods's avatar
bigrods
Helper III
6 months ago
Solved

Calculate Rolling Sickness Rate From Cumulative DataSet

Hi All, Hoping anyone can help - I have 3 tables, one of Employees, one of Sickness Absences, one Calendar and I would like to calculate a Rolling 12 month sickness absence % for each month; the iss...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    6 months ago

    Step 1) Create a proper Star-Schema model based on the data you have

     

    Since you have only 2 tables here you need to create dims and fact via PowerQuery. First create Employee and Date dimensions and create a proper FactSickness table to allocate your sickness data on a daily grain.

     

    Step 2) Create required measures

    Available FTE (Month) = 
VAR mStart = MAX ( DimDate[MonthStart] )
VAR mEnd   = EOMONTH ( mStart, 0 )
RETURN
CALCULATE (
    SUM ( FactEmployee[FTE] ),
    FILTER (
        FactEmployee,
        FactEmployee[Start Date] <= mEnd
            && ( ISBLANK ( FactEmployee[Termination Date] )
                 || FactEmployee[Termination Date] >= mStart )
    )
)

Available FTE (Rolling 12M) = 
CALCULATE (
    [Available FTE (Month)],
    DATESINPERIOD ( DimDate[MonthStart], MAX ( DimDate[MonthStart] ), -12, MONTH )
)

Sickness Lost (Rolling 12M) = 
CALCULATE (
    SUM ( FactSickness[DailyFTE_Lost] ),
    DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
)

Sickness Rate % (Rolling 12M) = 
DIVIDE ( [Sickness Lost (Rolling 12M)], [Available FTE (Rolling 12M)] )

     

    Step 3) Create your visual using correct field values

     

    Please fint the attached .pbix file for end-to-end solution.

srlabhe's avatar
srlabhe
Super User
6 months ago

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.

bigrods's avatar
bigrods
Helper III
6 months ago

Thanks so much for your reply, I tried the first reply and this worked successfully