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.

johnt75's avatar
johnt75
Super User
6 months ago

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
bigrods's avatar
bigrods
Helper III
6 months ago

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