Solved: Calculate Rolling 12 Month Sickness Ratio

bigrods · ‎04-04-2025

Hi All,

I'm trying to create a measure that gives me the rolling 12 month average sickness ratio of employees in our organisation.

I have 3 tables:

SummariseAbsences - all sickness absences recorded with Start Date, End Date, FTE (Ratio of Hours Worked per Day) and Total Days Lost (How many days lost)

Headcount - all staff including Start Date, Termination Date and FTE (Full Time Equivalent - i.e if they work full hours, per week: 1, if they work half hours per week: 0.5)

Calendar - Date table

SummariseAbsences (an Absence End Date of 31/12/4712 means the absence is still open)

AssignmentNo	First Name	Surname	Absence Start Date	Absence End Date	Classification	FTE	Total Days Lost
28553628	Bill	Brown	06/03/2024	06/03/2024	Short Term	1	1
32409705	John	Smith	23/12/2024	23/12/2024	Short Term	1	1
31383209	Jane	Jones	04/11/2024	31/12/4712	Long Term	0.96	112.32
28559843	Sarah	Todd	10/09/2024	31/12/4712	Long Term	0.52	89.44

Headcount

AssignmentNo	First Name	Surname	Start Date	Termination Date	FTE
28553628	Bill	Brown	01/04/2020		1
32409705	John	Smith	23/06/2022	15/01/2025	1
31383209	Jane	Jones	01/04/2020		0.96
28559843	Sarah	Todd	20/08/2017		0.52
32669532	Mike	Wilson	19/08/2024		1
28564640	Julia	Gibson	09/03/2021	17/10/2024	1
25226702	James	Green	03/04/2023	30/05/2024	1

I had created a measure and I can get the % per individual month, but I don't know how to get a rolling average, my measure is as follows:

The _absenttable is to calculate those absences that fall in the last 12 months, but for a rolling 12 month average I'm not sure if something different needs to be done?

The measure also references variable _empfte with an EmpMonth field in the Headcount table - I'd populated the table at the end of each month with a full list of staff members for each month (so employee details were duplicated every month but the EmpMonth column is different) but not sure if I need this for a rolling average and would a single list of employees with Start Date & Termination Date be ok? I'm aware that DATESINPERIOD would need to be used, but not sure how this all goes together, I seem to still be getting the % per month and not a rolling average %.

Sickness Absence Ratio % = 

var __startperiod = min('calendar'[Date])
var __endperiod = max('calendar'[Date])
var __empfte =  CALCULATE(SUM('Headcount'[TotalFTE]),FILTER(' Headcount','Headcount'[EmpMonth] = MIN('calendar'[Date])))

 var __absenttable = 
            ADDCOLUMNS(
                FILTER(
                    'SummariseAbsences',
                     ([Absence Start Date] >= __StartPeriod && 
                        [Absence Start Date] < __EndPeriod && [Absence End Date] <= __EndPeriod 
                    ) ||
                        ([Absence Start Date] >= __StartPeriod && 
                            [Absence Start Date] < __EndPeriod && 
                                [Absence End Date] > __EndPeriod
                        ) ||
                            ([Absence Start Date] < __StartPeriod && 
                                [Absence end Date] >= __StartPeriod && 
                                    [Absence End Date] < __EndPeriod
                            ) ||
                                ([Absence Start Date] < __StartPeriod &&
                                    [Absence End Date] > __EndPeriod
                                )
                ),

        
            "__FTESickHours",
            IF (
                'SummariseAbsences'[Absence Start Date] < __startperiod,
                IF (
                    'SummariseAbsences'[Absence End Date] > __endperiod,
                    'SummariseAbsences'[FTE]*(DATEDIFF ( __startperiod, __endperiod, DAY) + 1 ),
                    'SummariseAbsences'[FTE]*(DATEDIFF ( __startperiod, 'SummariseAbsences'[Absence End Date], DAY ) + 1 )
                    ),
                IF (
                    'SummariseAbsences'[Absence End Date] > __endperiod,
                    'SummariseAbsences'[FTE]* ( DATEDIFF ('SummariseAbsences'[Absence Start Date], __endperiod, DAY ) + 1 ),
                    'SummariseAbsences'[FTE]* (DATEDIFF ('SummariseAbsences'[Absence Start Date],'SummariseAbsences'[Absence End Date],DAY) + 1)
                    )
                ) 
            )
       
    var _ftesick = sumx(__absenttable,[__FTESickHours])
    var _absentratio = divide(_ftesick,__empfte,0)
 RETURN
 _absentratio

Many thanks for any help anyone can give! If anyone needs any more information please let me know - I hope posting this other measure details give some assistance and doesn't confuse matters

bigrods · ‎04-23-2025

Hi,

Thanks so much for the reply - this didn't quite work but I amended the DailyFTE table to check if the MIN and MAX of the calendar table was in the start and end ranges.

DailyFTETable = 
ADDCOLUMNS(
    'calendar',
        "EmpFTEAvailable",
            CALCULATE(SUMX('Rolling Sickness Headcount',
                var _start = 'Rolling Sickness Headcount'[Latest Start Date]
                var _end = COALESCE('Rolling Sickness Headcount'[Termination Date],today())
            return
                IF(AND(MIN('calendar'[Date]) >= _start, MAX('calendar'[Date]) <= _end), 'Rolling Sickness Headcount'[FTE],0))),
        "SicknessFTE",
            calculate(SUMX('SummariseAbsences',
                var _absStart = 'SummariseAbsences'[Absence Start Date]
                var _absEnd = IF('SummariseAbsences'[Absence End Date] = date(4712,12,31),today(),'SummariseAbsences'[Absence End Date])
            RETURN
                IF(AND(MIN('calendar'[Date]) >= _absStart, MAX('calendar'[Date]) <= _absEnd),'SummariseAbsences'[FTE],0))
            )
            )

RollingSickness = 
    var RollingDates = DATESINPERIOD('calendar'[Date],MAX('calendar'[Date]),-12,month)
    var DailyTable = ADDCOLUMNS(
        FILTER(DailyFTETable, DailyFTETable[Date] in RollingDates),
        "Ratio", DIVIDE([SicknessFTE], [EmpFTEAvailable],0)
    )
    RETURN
    AVERAGEX(DailyTable,[Ratio])

I then copied these to make "Short Term" and "Long Term" measures and my visual now works - thanks so much!

View solution in original post

v-pbandela-msft · ‎04-21-2025

Hi @bigrods,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept as Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

bhanu_gautam · ‎04-04-2025

@bigrods , Try using

Sickness Absence Ratio % =
VAR __startperiod = MIN('calendar'[Date])
VAR __endperiod = MAX('calendar'[Date])
VAR __empfte = CALCULATE(
SUM('Headcount'[TotalFTE]),
FILTER('Headcount', 'Headcount'[EmpMonth] = MIN('calendar'[Date]))
)

VAR __absenttable =
ADDCOLUMNS(
FILTER(
'SummariseAbsences',
(
([Absence Start Date] >= __startperiod && [Absence Start Date] < __endperiod && [Absence End Date] <= __endperiod) ||
([Absence Start Date] >= __startperiod && [Absence Start Date] < __endperiod && [Absence End Date] > __endperiod) ||
([Absence Start Date] < __startperiod && [Absence End Date] >= __startperiod && [Absence End Date] < __endperiod) ||
([Absence Start Date] < __startperiod && [Absence End Date] > __endperiod)
)
),
"__FTESickHours",
IF(
'SummariseAbsences'[Absence Start Date] < __startperiod,
IF(
'SummariseAbsences'[Absence End Date] > __endperiod,
'SummariseAbsences'[FTE] * (DATEDIFF(__startperiod, __endperiod, DAY) + 1),
'SummariseAbsences'[FTE] * (DATEDIFF(__startperiod, 'SummariseAbsences'[Absence End Date], DAY) + 1)
),
IF(
'SummariseAbsences'[Absence End Date] > __endperiod,
'SummariseAbsences'[FTE] * (DATEDIFF('SummariseAbsences'[Absence Start Date], __endperiod, DAY) + 1),
'SummariseAbsences'[FTE] * (DATEDIFF('SummariseAbsences'[Absence Start Date], 'SummariseAbsences'[Absence End Date], DAY) + 1)
)
)
)

VAR _ftesick = SUMX(__absenttable, [__FTESickHours])
VAR _absentratio = DIVIDE(_ftesick, __empfte, 0)

VAR Rolling12Months =
CALCULATE(
_absentratio,
DATESINPERIOD('calendar'[Date], MAX('calendar'[Date]), -12, MONTH)
)

RETURN
Rolling12Months

Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

bigrods · ‎04-04-2025

Hi @bhanu_gautam

I was getting around 11 - 12% for the rolling 12 month sickness ratio which is not correct, this should be nearer to 6 % as in the chart below:

I have amended the measure as I wonder if there needs to be some recognition of employees that join & leave midway through the year? I created a second table called _empftetable but still getting the same figures as above for the rolling average:

Rolling Sickness Absence Ratio % = 

var __startperiod = min('calendar'[Date])
var __endperiod = max('calendar'[Date])
 var __absenttable = 
            ADDCOLUMNS(
                FILTER(
                    'SummariseAbsences',
                     ([Absence Start Date] >= __StartPeriod && 
                        [Absence Start Date] < __EndPeriod && [Absence End Date] <= __EndPeriod 
                    ) ||
                        ([Absence Start Date] >= __StartPeriod && 
                            [Absence Start Date] < __EndPeriod && 
                                [Absence End Date] > __EndPeriod
                        ) ||
                            ([Absence Start Date] < __StartPeriod && 
                                [Absence end Date] >= __StartPeriod && 
                                    [Absence End Date] < __EndPeriod
                            ) ||
                                ([Absence Start Date] < __StartPeriod &&
                                    [Absence End Date] > __EndPeriod
                                )
                ),

        
            "__FTESickHours",
            IF (
                'SummariseAbsences'[Absence Start Date] < __startperiod,
                IF (
                    'SummariseAbsences'[Absence End Date] > __endperiod,
                    'SummariseAbsences'[FTE]*(DATEDIFF ( __startperiod, __endperiod, DAY) + 1 ),
                    'SummariseAbsences'[FTE]*(DATEDIFF ( __startperiod, 'SummariseAbsences'[Absence End Date], DAY ) + 1 )
                    ),
                IF (
                    'SummariseAbsences'[Absence End Date] > __endperiod,
                    'SummariseAbsences'[FTE]* ( DATEDIFF ('SummariseAbsences'[Absence Start Date], __endperiod, DAY ) + 1 ),
                    'SummariseAbsences'[FTE]* (DATEDIFF ('SummariseAbsences'[Absence Start Date],'SummariseAbsences'[Absence End Date],DAY) + 1)
                    )
                ) 
            )
var _empftetable = 
    ADDCOLUMNS(
        FILTER(
            'Headcount',
                'Headcount'[Termination Date] = BLANK() || 'Headcount'[Termination Date] > EDATE(max('calendar'[Date]),-12)
        ),
        "_EmpFTEHours",
            'Headcount'[FTE]
    )

    var __empfte = SUMX(_empftetable,[_EmpFTEHours])
    var _ftesick = sumx(__absenttable,[__FTESickHours])
    var _absentratio = divide(_ftesick,__empfte,0)
    var _rolling12months = CALCULATE(_absentratio,DATESINPERIOD('calendar'[Date],MAX('calendar'[Date]),-12,month))
 RETURN
 _rolling12months

Again, many thanks!

bigrods · ‎04-04-2025

I've been playing around with it more and getting closer but not quite there, I've amended the measure as follows:

Rolling Sickness Absence Ratio % = 

var __startperiod = min('calendar'[Date])
var __endperiod = max('calendar'[Date])
 var __absenttable = 
            ADDCOLUMNS(
                FILTER(
                    'SummariseAbsences',
                     ([Absence Start Date] >= __StartPeriod && 
                        [Absence Start Date] < __EndPeriod && [Absence End Date] <= __EndPeriod 
                    ) ||
                        ([Absence Start Date] >= __StartPeriod && 
                            [Absence Start Date] < __EndPeriod && 
                                [Absence End Date] > __EndPeriod
                        ) ||
                            ([Absence Start Date] < __StartPeriod && 
                                [Absence end Date] >= __StartPeriod && 
                                    [Absence End Date] < __EndPeriod
                            ) ||
                                ([Absence Start Date] < __StartPeriod &&
                                    [Absence End Date] > __EndPeriod
                                )
                ),

        
            "__FTESickHours",
            IF (
                'SummariseAbsences'[Absence Start Date] < __startperiod,
                IF (
                    'SummariseAbsences'[Absence End Date] > __endperiod,
                    'SummariseAbsences'[FTE]*(DATEDIFF ( __startperiod, __endperiod, DAY) + 1 ),
                    'SummariseAbsences'[FTE]*(DATEDIFF ( __startperiod, 'SummariseAbsences'[Absence End Date], DAY ) + 1 )
                    ),
                IF (
                    'SummariseAbsences'[Absence End Date] > __endperiod,
                    'SummariseAbsences'[FTE]* ( DATEDIFF ('SummariseAbsences'[Absence Start Date], __endperiod, DAY ) + 1 ),
                    'SummariseAbsences'[FTE]* (DATEDIFF ('SummariseAbsences'[Absence Start Date],'SummariseAbsences'[Absence End Date],DAY) + 1)
                    )
                ) 
            )
var _empftetable = 
    ADDCOLUMNS(
        FILTER(
            'Headcount',
                ('Headcount'[Latest Start Date] < __startperiod && 'Headcount'[Termination Date] > __startperiod) ||
                ('Headcount'[Latest Start Date] < __startperiod && 'Headcount'[Termination Date] = BLANK()) ||
                ('Headcount'[Latest Start Date] >= __startperiod && 'Headcount'[Latest Start Date] <= __endperiod) ||
                ('Headcount'[Latest Start Date] >= __startperiod && 'Headcount'[Latest Start Date] <= __endperiod && 'Headcount'[Termination Date] = blank())
        ),
        "_EmpFTEHours",
            IF(
                'Headcount'[Latest Start Date] < __startperiod,
                IF(('Headcount'[Termination Date] >= __startperiod && 'Headcount'[Termination Date] > __endperiod) || ('Headcount'[Termination Date] = BLANK()),
                        ' Headcount'[FTE]*DATEDIFF(__startperiod,__endperiod,day)+1,
                        IF('Headcount'[Termination Date] >= __startperiod,
                              'Headcount'[FTE]*DATEDIFF(__startperiod,'Headcount'[Termination Date],day)+1)),
                IF('Headcount'[Termination Date] <= __endperiod,
                    'Headcount'[FTE]*DATEDIFF('Headcount'[Latest Start Date],'Headcount'[Termination Date],day)+1,
                    'Headcount'[FTE]*DATEDIFF('Headcount'[Latest Start Date],__endperiod,day)+1
                )
    )
    )
    var __empfte = SUMX(_empftetable,[_EmpFTEHours])
    var _ftesick = sumx(__absenttable,[__FTESickHours])
    var _absentratio = divide(_ftesick,__empfte,0)
    var _rolling12months = CALCULATE(_absentratio,DATESINPERIOD('calendar'[Date],MAX('calendar'[Date]),-12,month))
 RETURN
 _rolling12months

..BUT what I think I actually need is some kind of summary table that summarises both the headcount and the Absences table with a count of each individual day and the sum of FTE and Sickness FTE for that day - then the DATESINPERIOD will work.

So something like this?

Date	Employee FTE Available	Sickness FTE
01-March-2024	87.65	8.23
02-March-2024	87.65	7.67
03-March-2024	89.23	9.25

these fields would need to take into account the Start Date and Termination Date of each employee, and the Start Date and End Date of each absence. I think this is the way to do it?? But I would appreciate any help!

v-pbandela-msft · ‎04-09-2025

Hi @bigrods,

Thank you for reaching out in Microsoft Community Forum.

Thank you @bhanu_gautam for the helpful response.

if you're aiming for day-level precision to accurately calculate a rolling 12-month sickness absence ratio, then creating a virtual table that aggregates FTEs and sickness FTEs per day is indeed the most reliable and scalable approach.

Please follow below steps to acheive the error;

1. Create a virtual daily FTE table in a measure using below DAX;

DailyFTETable =
ADDCOLUMNS (
'Calendar',
"EmpFTEAvailable",
CALCULATE (
SUMX (
'Headcount',
VAR Start = 'Headcount'[Start Date]
VAR End = COALESCE('Headcount'[Termination Date], TODAY())
RETURN
IF (
'Calendar'[Date] >= Start && 'Calendar'[Date] <= End,
'Headcount'[FTE],
0
)
)
),
"SicknessFTE",
CALCULATE (
SUMX (
'SummariseAbsences',
VAR AbsStart = 'SummariseAbsences'[Absence Start Date]
VAR AbsEnd = IF('SummariseAbsences'[Absence End Date] = DATE(4712,12,31), TODAY(), 'SummariseAbsences'[Absence End Date])
RETURN
IF (
'Calendar'[Date] >= AbsStart && 'Calendar'[Date] <= AbsEnd,
'SummariseAbsences'[FTE],
0
)
)
)
)

2.Use it to calculate your rolling ratio:

Rolling Sickness Absence Ratio % =
VAR RollingDates =
DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH)

VAR DailyTable =
ADDCOLUMNS (
FILTER ( DailyFTETable, 'Calendar'[Date] IN RollingDates ),
"Ratio", DIVIDE([SicknessFTE], [EmpFTEAvailable], 0)
)

RETURN
AVERAGEX ( DailyTable, [Ratio] )

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

bigrods · ‎04-14-2025

Hi @v-pbandela-msft

Thanks so much for the reply; I have started to work on your suggestion - I created the first measure but getting an error

"A single value for column 'date' in table 'calendar' cannot be determined"

(Apologies - the Headcount table has been renamed 'Rolling Sickness Headcount')

My Calendar table looks as below but there is no actual relationship between this and any other table, should there be?

Many thanks for any assistance you could give!

v-pbandela-msft · ‎04-15-2025

Hi @bigrods,

Thank you for reaching out in Microsoft Community Forum.

please follow below steps for required solution;

1. Create Relationships in your data model
-->'Calendar'[Date] → 'SummariseAbsences'[Absence Start Date]

-->'Calendar'[Date] → 'Headcount'[Start Date] (or equivalent date field)

2.Instead of referencing 'Calendar'[Date] directly, build your daily FTE measure using ADDCOLUMNS + SUMX, like below;

Rolling Daily Table =
VAR _Table =
ADDCOLUMNS (
'Calendar',
"EmpFTEAvailable",
SUMX (
FILTER (
'Headcount',
'Calendar'[Date] >= 'Headcount'[Start Date]
&& (
ISBLANK('Headcount'[Termination Date])
|| 'Calendar'[Date] <= 'Headcount'[Termination Date]
)
),
'Headcount'[FTE]
),
"SicknessFTE",
SUMX (
FILTER (
'SummariseAbsences',
'Calendar'[Date] >= 'SummariseAbsences'[Absence Start Date]
&& (
'Calendar'[Date] <=
IF (
'SummariseAbsences'[Absence End Date] = DATE(4712, 12, 31),
TODAY(),
'SummariseAbsences'[Absence End Date]
)
)
),
'SummariseAbsences'[FTE]
)
)
RETURN
_Table

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

bigrods · ‎04-23-2025

Hi,

Thanks so much for the reply - this didn't quite work but I amended the DailyFTE table to check if the MIN and MAX of the calendar table was in the start and end ranges.

DailyFTETable = 
ADDCOLUMNS(
    'calendar',
        "EmpFTEAvailable",
            CALCULATE(SUMX('Rolling Sickness Headcount',
                var _start = 'Rolling Sickness Headcount'[Latest Start Date]
                var _end = COALESCE('Rolling Sickness Headcount'[Termination Date],today())
            return
                IF(AND(MIN('calendar'[Date]) >= _start, MAX('calendar'[Date]) <= _end), 'Rolling Sickness Headcount'[FTE],0))),
        "SicknessFTE",
            calculate(SUMX('SummariseAbsences',
                var _absStart = 'SummariseAbsences'[Absence Start Date]
                var _absEnd = IF('SummariseAbsences'[Absence End Date] = date(4712,12,31),today(),'SummariseAbsences'[Absence End Date])
            RETURN
                IF(AND(MIN('calendar'[Date]) >= _absStart, MAX('calendar'[Date]) <= _absEnd),'SummariseAbsences'[FTE],0))
            )
            )

RollingSickness = 
    var RollingDates = DATESINPERIOD('calendar'[Date],MAX('calendar'[Date]),-12,month)
    var DailyTable = ADDCOLUMNS(
        FILTER(DailyFTETable, DailyFTETable[Date] in RollingDates),
        "Ratio", DIVIDE([SicknessFTE], [EmpFTEAvailable],0)
    )
    RETURN
    AVERAGEX(DailyTable,[Ratio])

I then copied these to make "Short Term" and "Long Term" measures and my visual now works - thanks so much!

Calculate Rolling 12 Month Sickness Ratio

Helpful resources

Join our Fabric User Panel

Power BI Monthly Update - June 2025

Fabric Community Update - June 2025

How to Get Your Question Answered Quickly

Become a Certified Power BI Data Analyst!