Break-Down Employee Sickness Days Lost with Date Table

[bigrods]

Hi,

I'm trying to work on a measure that calculates the sum of Days lost due to Employee sickness.

I have created a temporary table in my measure and I also have a seperate Date table (called Calendar)

The issue I'm having is that employee sickness can span across months, and when I select a certain month on the page it returns the total days for the entire sickness, not broken down by how much specifically lost in that month.

On the attached images, if I select April 2024 on the page I get 179 Days - this is the Total Days Lost for all Sickness recorded that began in April, the actual answer should be 20:

4 Days from Assignment No. 30761466 (2nd - 5th April) and 16 Days from Assignment No. 27160971 (began 15th April)

In my measure I tried to create a table column that calculates the DATEDIFF for the start and end period of the Calendar table, so I'd hoped this would then calculate the Days Lost accordingly. If anyone could help I would be very grateful!

Many thanks!

[v-linhuizh-msft · Accepted solution]

Hi @bigrods ,

Here is the modified formula:

New Sickness FTE Measure = 
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 )
        ),
        "_FTEStartDate", 'SummariseAbsences'[Absence Start Date],
        "_FTEEndDate", 'SummariseAbsences'[Absence End Date],
        "_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
                        )
                )
            )
    ) 
// IF('SummariseAbsences'[Absence Start Date]<=_startperiod,
// IF('SummariseAbsences'[Absence End Date]=DATE(4712,12,31)||'SummariseAbsences'[Absence Start Date]>=_endperiod,'SummariseAbsences'[FTE]*(DATEDIFF(_startperiod,_endperiod,DAY)+1),
// 'SummariseAbsences'[FTE]*(DATEDIFF(_startperiod,'SummariseAbsences'[Absence End Date],DAY)+1)),
// if('SummariseAbsences'[Absence Start Date]<=_endperiod&&('SummariseAbsences'[Absence End Date]=DATE(4712,12,31)||'SummariseAbsences'[Absence End Date]>=_endperiod),
// 'SummariseAbsences'[FTE]*(DATEDIFF(_startperiod,_endperiod,DAY)+1),
// 'SummariseAbsences'[FTE]*(DATEDIFF(_startperiod,[Absence End Date],DAY)+1))),
//"_FTEDaysLost",'SummariseAbsences'[Total Days Lost])
VAR _ftedayslost =
    SUMX ( _absenttable, [_FTESickHours] )
RETURN
    _ftedayslost

The logic for calculating the _FTESickHours column is modified, and the variable _ftedayslost is modified to sum _FTESickHours.

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[bigrods]

Thanks so much this has worked!