calculate absence Rate and Lost Time

[P_B]

I want to calculate Lost Time rate on a given day and later annual average of 365 day's absence rate and lost time rate. Below is the sample data

Emp ID	Payroll Code	Broad Category	Specific Category	Date	Hours
234	REG	Worked	Regular	5/1/2018	8
234	REG	Worked	Regular	5/2/2018	6
578	SKS	Absence	Sick	5/2/2018	2
964	REG	Worked	Regular	5/3/2018	8
452	REG	Worked	Regular	5/4/2018	8
452	FML	Absence	FML	5/7/2018	8
452	REG	Worked	Regular	5/8/2018	7.3
412	SKS	Absence	Sick	5/8/2018	0.7
412	REG	Worked	Regular	5/9/2018	8
412	REG	Worked	Regular	5/10/2018	8
569	FML	Absence	FML	5/11/2018	8
569	REG	Worked	Regular	5/14/2018	8
673	REG	Worked	Regular	5/15/2018	4
765	SKS	Absence	Sick	5/15/2018	4
879	REG	Worked	Regular	5/16/2018	8

Mathematical Formula for lost time rate  = Hours lost/ Regular hours work 

Total employees = 100

Total Hours lost in a day = 40 hours

Workday = 8 hours

Lost Time Rate = 40/(100*8) 

I was able to calculate the sum of lost hours by each date (as a measure) using the following DAX code :

SumofLostHoursbyDate = CALCULATE(SUMX('Time and Labor - Absenteeism1','Time and Labor - Absenteeism1'[Hours]),FILTER('Time and Labor - Absenteeism1','Time and Labor - Absenteeism1'[Broad Category]="Absence")) 

But not sure how to do the rest of the calculation...

I would prefer to save this day lost time rate in a table so that I can refer/use it for annual average calculation. 

Thank you so much for your help !!! 

[v-lid-msft · Accepted solution]

Hi @P_B ,

Based on your addition information, I think that you are looking for a calculated table which contain the total lost date and lost rate for each day, we can use the following formula to create such a table:

NewTable = 
VAR TotalEmployee = [TotalActiveEmployee] -- if this measure is dynamic for every day, you should change the following formula.
VAR TotalTime = 8 * TotalEmployee
RETURN
    SELECTCOLUMNS (
        ADDCOLUMNS (
            ADDCOLUMNS (
                SELECTCOLUMNS (
                    DISTINCT ( 'Time and Labor - Absenteeism1'[Date] ),
                    "Date-c", [Date]
                ),
                "SumOfHourByDate", SUMX (
                    FILTER (
                        'Time and Labor - Absenteeism1',
                        'Time and Labor - Absenteeism1'[Broad Category] = "Absence"
                            && 'Time and Labor - Absenteeism1'[Date] = [Date-c]
                    ),
                    [Hours]
                )
            ),
            "Rate", FORMAT ( [SumOfHourByDate] / TotalTime, "Percent" )
        ),
        "Date", [Date-c],
        "SumOfHourByDate", [SumOfHourByDate],
        "LostTimeRate", [Rate]
    )

I hope you do not mind that I reply here to help other people can find solution if they have similar situation. 

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JamesLindsay101]

Hi,

I am looking to put together a similar measure which I can use on a graph to track how the Annualised sickness rate has changed over time.

Does the above solution take into consideration when sickness periods that are across multiple days span over multiple months? e.g. if someone is ill from the 29th March to the 3rd April, how does the above solution cope with that?

Edit: I've just noticed the table above has an absence event (row) for each day an individual is absent. My response above is referring to when the absence table has an absence event (row) for each whole absence period for an individual. Let me know if the above methodolgy is malleable to this situation

Thanks,

James

[P_B]

Thank you! It worked. 

[v-lid-msft]

Hi @P_B ,

We can use the following measure to meet your requirement.

Rate = 
VAR TotalLostTime =
    SUMX (
        FILTER (
            'Time and Labor - Absenteeism1',
            'Time and Labor - Absenteeism1'[Broad Category] = "Absence"
        ),
        [Hours]
    )
VAR TotalEmployee = 100 -- You can get the total employees number from the other table which should contain whole employee.
VAR TotalTime =
    IF (
        SELECTEDVALUE ( 'Time and Labor - Absenteeism1'[Date].[Year] )
            < YEAR ( TODAY () ),
        8 * TotalEmployee * 365,
        8 * TotalEmployee
            * DATEDIFF ( DATE ( YEAR ( TODAY () ), 1, 1 ), TODAY (), DAY )
    )
RETURN
    FORMAT ( TotalLostTime / TotalTime, "Percent" )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kamalmsharma]

Hi,

Your formula is correct for numerator of Lost Time Rate. For denominator you may use following :
Total Hours = DISTINCTCOUNT('Time and Labor - Absenteeism1'[Emp ID])*8

You may then create a table visual using Date and Lost Time Rate as columns.

Best regards,

Kamal

www.addendanalytics.com

[amitchandak]

This is a bit tricky and various ways to do.

If you looking for a range that is controlled from the slicer.  Then you can take the date diff of min and max date of the slicer.

Another way is to do count distinct of day and employee and multiple those.