Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AvdBosch
Frequent Visitor

Count number of days between two dates within filter context

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!

1 ACCEPTED SOLUTION
ray_codex
Resolver I
Resolver I

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.



 

View solution in original post

4 REPLIES 4
ray_codex
Resolver I
Resolver I

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.



 

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

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.

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

 

AvdBosch_0-1701786107969.png

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.