Count number of days between two dates within filter context

[AvdBosch]

Hey there, i'm in a bit of a pickle;

I want to count the number of days a worker is sick within the filtercontext.

For example, 

• WorkId; startdate;      enddate;      total sick days
• 000104; 01-01-2023; 14-01-2023; 14
• 000105; 30-12-2022; 07-01-2023; 9
• 000106; 07-01-2023; 21-01-2023; 14

worker "000104" is sick for a total of 14 days when it is in between the filtercontext.

The total of sick days has to change to 7 when the filtercontext changes (with a slicer e.g.) to start in 07-01-2023 till 31-12-2023.

It is also possible for a worker to have a sick start date before and a sick end date after the filtercontext. In this case it has to return the number of days in the filtercontext. So the number of sick days can't exceed the number of days in the filtercontext.

I have tried this but it doesn't seem to work because of it returning a table instead of a single value. 

CALCULATE(
    COUNTROWS('Kalender (2020 - 2024)'),
    GENERATE(
        VALUES('Kalender (2020 - 2024)'[Date]),
        FILTER(
            Verzuim,
            CONTAINS(
                DATESBETWEEN(
                    'Kalender (2020 - 2024)'[date],
                    SELECTCOLUMNS( Verzuim, "Begindatum", Verzuim[Begindatum] ),
                    SELECTCOLUMNS( Verzuim, "Einddatum", Verzuim[Einddatum] )
                ),
                [date],
                'Kalender (2020 - 2024)'[Date]
               
            )
        )
    )
)

"Verzuim" = table with a sick registration per row. A worker can occur multiple times

"Verzuim"[Begindatum] = Sick start date

"Verzuim"[Einddatum] = Sick end date

"Kalender (2020 - 2024)" is my calendar table.

There has been said i could use a SUMMARIZE but i am not familiar with the workings of that function.

Many thanks in advance!

[ray_codex · Accepted solution]

I did something like this last week, only with revenue. I replaced my table and column names with yours, I think this should work for you too:

VAR res_table =
    ADDCOLUMNS (
        VERZUIM,
        "_sickdays",
            VAR days =
                SWITCH (
                    TRUE (),
                    // Scenario 1: Range is fully between start & end of date filter
                    VERZUIM[Begindatum]
                        >= MIN ( 'Kalender (2020 - 2024)'[Date] )
                        && VERZUIM[Einddatum] <= MAX ( 'Kalender (2020 - 2024)'[Date] ),
                        INT ( VERZUIM[Einddatum] - VERZUIM[Begindatum] ) + 1,
                    // Scenario 2: Range end beyond end of date filter
                    VERZUIM[Begindatum]
                        >= MIN ( 'Kalender (2020 - 2024)'[Date] )
                        && VERZUIM[Einddatum] > MAX ( 'Kalender (2020 - 2024)'[Date] ),
                        INT ( MAX ( 'Kalender (2020 - 2024)'[date] ) - VERZUIM[Begindatum] ) + 1,
                    // Scenario 3: Range start before start of date filter
                    VERZUIM[Begindatum]
                        < MIN ( 'Kalender (2020 - 2024)'[Date] )
                        && VERZUIM[Einddatum] <= MAX ( 'Kalender (2020 - 2024)'[Date] ),
                        INT ( VERZUIM[Einddatum] - MIN ( 'Kalender (2020 - 2024)'[Date] ) ) + 1,
                    // Scenario 0: Range starts before filter start and ends beyond filter end
                    VERZUIM[Begindatum]
                        < MIN ( 'Kalender (2020 - 2024)'[Date] )
                        && VERZUIM[Einddatum] > MAX ( 'Kalender (2020 - 2024)'[Date] ),
                        INT (
                            MAX ( 'Kalender (2020 - 2024)'[Date] ) - MIN ( 'Kalender (2020 - 2024)'[Date] )
                        ) + 1,
                    0
                )
            RETURN
                // exclude negative days
                IF ( days <= 0, 0, days )
    )
VAR result =
    SUMX ( res_table, [_sickdays] )
RETURN
    result

Maybe it needs a little alteration (SUMX to AVERAGEX), but if you have any questions let me know.

[AvdBosch]

Hi ray, 

thanks!

the code makes sense but i''m having some trouble understanding what is outputted from "res_table"

i'm guessing that i have to alter the "result" var in order to get the right output.. but how

[ray_codex]

What basically is done is taking the whole verzuim table and adding a column with the days that are in scope in your filtered date dimension (Kalender, I'm Dutch 😉).

If you have Dax Studio, you can connect it to your Power BI file, type EVALUATE and copy everything from ADDCOLUMNS until just before "return", and paste it below. If you run it you will see how the table looks, if my code is working with you there should see your verzuim table with a column at the end called "_sickdays". This column will be the same as the days between the sick start and end date, cause there is no date filter applied.

What the result variable does is interate this table and sum all sickdays in any filter filter context ( I dont know how your model and table looks). If you change the slicer for the kalender in Power BI, this will give results according to your set dateperiod.

[AvdBosch]

so it does indeed return the right amount of sick days in dax studio. I had some bidirectional relationships, after changing back to monodirectional the dates were right! Now you can even spot registration errors when the # sick_days exceeds the expected amount.

Many thanks, fellow dutchie