Networking Days based on Slicer or values in a table

[RJS22]

I am looking at anylsing absence.
For each absence there is a start and end date (some end dates are blank).
We also have a slicer (linked to a date table) with a start and end date for the report.
When the slicer start and end dates are set I want to count the Network days of absence between those 2 dates. 
In explaination - include all absences the are occuring during the slicer date range (those that start before or during the date range but end during or after it) but only count the networking days between the two dates
 For Example.
I have an absence that runs from 18th July 2025 to 23rd July 2025. The slicer is set for the  21st to 25th of July.
The networkdays calculation needs to start on the 21st July (start of the slicer range) and end of the 23rd (end of the absence).
Similarly if an absence ends after the slicer end date the end date should be the slicer end date.

See below the filter has been set for the 14th to the 16th June.
Shown below are the absence start and end dates the start and end date used in the networking days calc and the total.
For the individual rows it is calculating ok ,for the total I would like it to add up the individual network days.

My measure looks like -

Networking Days =

VAR CalendarStartDate = MIN ( DateTable[Date] )

VAR CalendarEndDate = MAX ( DateTable[Date] )

VAR DealBlankEndDate = IF(MIN('Staff Absences'[Absence End Date]) = BLANK(),
CalendarEndDate, MIN('Staff Absences'[Absence End Date]))

VAR Days = SUMX(FILTER('Staff Absences',   'Staff Absences'[Absence End Date] = BLANK()

   && 'Staff Absences'[Absence Start Date] <= CalendarEndDate

  ||

   'Staff Absences'[Absence End Date] >= CalendarStartDate

   && 'Staff Absences'[Absence Start Date] <= CalendarEndDate

   ),

NETWORKDAYS(

    MAX(CalendarStartDate,'Staff Absences'[Absence Start Date]),

    MIN(CalendarEndDate,DealBlankEndDate)

    , 1

    , 'Bank Holidays'

))

RETURN

IF(ISBLANK(Days),0,Days)

Absence Start Date	Absence End Date	Absence Type	Index
Mon 12 Jun 2023	12-Jun-23	Sickness	510
Fri 16 Jun 2023	16-Jun-23	Other paid authorised absence, e.g. compassionate leave	504
Fri 16 Jun 2023	16-Jun-23	Other paid authorised absence, e.g. compassionate leave	505
Fri 16 Jun 2023	16-Jun-23	Other paid authorised absence, e.g. compassionate leave	506
Thu 15 Jun 2023	15-Jun-23	Other paid authorised absence, e.g. compassionate leave	507
Thu 15 Jun 2023	15-Jun-23	Other paid authorised absence, e.g. compassionate leave	508
Mon 12 Jun 2023	23-Jun-23	Sickness

MTIA

[sevenhills · Accepted solution]

I think you are close to the solution.

Reference:

https://www.daxpatterns.com/events-in-progress/

Networking Days =
VAR CalendarStartDate = MIN(DateTable[Date])
VAR CalendarEndDate = MAX(DateTable[Date])
VAR Days =
    SUMX(
        FILTER(
            'Staff Absences',
            (
                ISBLANK('Staff Absences'[Absence End Date])
                    && 'Staff Absences'[Absence Start Date] <= CalendarEndDate
            )
            || (
                'Staff Absences'[Absence End Date] >= CalendarStartDate
                    && 'Staff Absences'[Absence Start Date] <= CalendarEndDate
            )
        ),
        VAR StartDate = MAX(CalendarStartDate, 'Staff Absences'[Absence Start Date])
        VAR EndDate =
            IF(
                ISBLANK('Staff Absences'[Absence End Date]),
                CalendarEndDate,
                MIN(CalendarEndDate, 'Staff Absences'[Absence End Date])
            )
        RETURN
            NETWORKDAYS(StartDate, EndDate, 1, 'Bank Holidays')
    )
RETURN
    IF(ISBLANK(Days), 0, Days)

See if this works for you, then we can tweak for little more simpler Dax code ... 

[FBergamaschi]

Can you please show the data model and the detail of the visual settings? I am not sure I understand how you involve the calendar table into the visual

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI