how to get illness days per periode when illness spans multiple periods

[Anonymous]

Hi Have a table with illness records, containing employee nr,  startdate enddate.

Now i want to report on number of illness days in a periode, but sometimes an illness records spans mutiples periods.

So when an employee illness start date is 27-1-2020 and end date is 10-2-2020, for january it has to count 5 days (27-31), and for february its has to count 10 days.

Illnes records

I have next measure, but gives strange results:

illnes days in period:=

VAR startDate =
MAX ( MIN ( Verzuim[Begindatum] ); MIN (Kalender[Datum]))
VAR endDate =
MIN ( MAX (Verzuim[Einddatum]); MAX (Kalender[Datum] ) )
RETURN
CALCULATE(
IF ( endDate >= startDate; DATEDIFF ( startDate; endDate; DAY )
)

However, this give wrong results when 1 employee has multiple illness records in a period, and also when the end date is greater than the max(kalender[date]). E.g. for january, when the ilnness start date = 27-1-2020, it gives 4 days, but should be 5.

And for employee 000025, number of thays in january 2020 should be 5 ,not 30.
I don't understand why employee 000025 gets 30 illness days and 000265 4, while they have the same illness start date, i guess it has to do with the fact that 000025 has multiple records from the past?

ilness days matrix

[OwenAuger]

Hi @Anonymous 

I recommend you write a measure with similar logic to the code you posted, that iterates over the Verzuim table using SUMX.

Assuming that there are no absence periods for the same employee that overlap, this should work fine.

illness days in period = 
VAR MinKalenderDatum =
    MIN ( Kalender[Datum] )
VAR MaxKalenderDatum =
    MAX ( Kalender[Datum] )
RETURN
    SUMX ( 
        Verzuim,
        VAR startDate =
            MAX ( Verzuim[Begindatum], MinKalenderDatum )
        VAR endDate =
            MIN ( Verzuim[Einddatum], MaxKalenderDatum )
        RETURN
            MAX ( BLANK (), endDate - startDate + 1 )
    )

I added some variables to avoid repeated evaluation of min/max of Kalender[Datum], and subtracted startDate from endDate rather than using DATEDIFF

Sample PBIX here in case useful.

Regards,

Owen

[Anonymous]

Hi Owen,

Thanks a lot for your respons, it got me on the right way. I prefer datediff because your measure gives decimals, not exact numbers of days. And the measure only has to add one day if the end date >= MAX(Kalender[Datum]), in  other cases it doesn't have to add 1 day. Now I noticed another thing: The measure does not take into account the illness cases with blank end date, so still open. E.g. now it is february, and for january it should use MAX(kalender[datum]) for blank end dates. So far I have this:

verzuimdagen2:=
VAR MinKalenderDatum =
MIN ( Kalender[Datum] )
VAR MaxKalenderDatum =
MAX ( Kalender[Datum] )
RETURN
SUMX (
Verzuim;
VAR startDate =
MAX ( Verzuim[Begindatum]; MinKalenderDatum )
VAR endDate =
MIN ( Verzuim[Einddatum]; MaxKalenderDatum )
RETURN
IF(endDate >= MaxKalenderDatum;
IF(endDate >= startDate;DATEDIFF(startDate;endDate+1;DAY));
IF(endDate >= startDate;DATEDIFF(startDate;endDate;DAY))
))

[OwenAuger]

Hi again @Anonymous 

One way is just to change your expression for endDate to handle the BLANK case by using MaxKalenderDatum when Verzuim[Einddatum] is blank:

verzuimdagen2 :=
VAR MinKalenderDatum =
    MIN ( Kalender[Datum] )
VAR MaxKalenderDatum =
    MAX ( Kalender[Datum] )
RETURN
    SUMX (
        Verzuim;
        VAR startDate =
            MAX ( Verzuim[Begindatum]; MinKalenderDatum )
        VAR endDate =
            IF (
                ISBLANK ( Verzuim[Einddatum] );
                MaxKalenderDatum;
                MIN ( Verzuim[Einddatum]; MaxKalenderDatum )
            )
        RETURN
            IF (
                endDate >= MaxKalenderDatum;
                IF ( endDate >= startDate; DATEDIFF ( startDate; endDate + 1; DAY ) );
                IF ( endDate >= startDate; DATEDIFF ( startDate; endDate; DAY ) )
            )
    )

Hopefully something like that works.

Regards,

Owen