Networking Days based on Slicer or values in a table

Question

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.&nbsp;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&nbsp;For Example.I have an absence that runs from 18th July 2025 to 23rd July 2025. The slicer is set for the&nbsp; 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 =&nbsp;MIN ( DateTable[Date] )VAR CalendarEndDate =&nbsp;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', &nbsp; 'Staff Absences'[Absence End Date] = BLANK()&nbsp; &nbsp;&amp;&amp; 'Staff Absences'[Absence Start Date] &lt;= CalendarEndDate&nbsp; ||&nbsp; &nbsp;'Staff Absences'[Absence End Date] &gt;= CalendarStartDate&nbsp; &nbsp;&amp;&amp; 'Staff Absences'[Absence Start Date] &lt;= CalendarEndDate&nbsp; &nbsp;),NETWORKDAYS(&nbsp; &nbsp; MAX(CalendarStartDate,'Staff Absences'[Absence Start Date]),&nbsp; &nbsp; MIN(CalendarEndDate,DealBlankEndDate)&nbsp; &nbsp; , 1&nbsp; &nbsp; , 'Bank Holidays'))RETURNIF(ISBLANK(Days),0,Days)Absence Start DateAbsence End DateAbsence TypeIndexMon 12 Jun 202312-Jun-23Sickness510Fri 16 Jun 202316-Jun-23Other paid authorised absence, e.g. compassionate leave504Fri 16 Jun 202316-Jun-23Other paid authorised absence, e.g. compassionate leave505Fri 16 Jun 202316-Jun-23Other paid authorised absence, e.g. compassionate leave506Thu 15 Jun 202315-Jun-23Other paid authorised absence, e.g. compassionate leave507Thu 15 Jun 202315-Jun-23Other paid authorised absence, e.g. compassionate leave508Mon 12 Jun 202323-Jun-23Sickness&nbsp;&nbsp;MTIA&nbsp;&nbsp;

sevenhills · Accepted Answer

I think you are close to the solution.
&nbsp;
Reference:
https://www.daxpatterns.com/events-in-progress/
&nbsp;
Networking Days =
VAR CalendarStartDate = MIN(DateTable[Date])
VAR CalendarEndDate = MAX(DateTable[Date])
VAR Days =
    SUMX(
        FILTER(
            'Staff Absences',
            (
                ISBLANK('Staff Absences'[Absence End Date])
                    &amp;&amp; 'Staff Absences'[Absence Start Date] &lt;= CalendarEndDate
            )
            || (
                'Staff Absences'[Absence End Date] &gt;= CalendarStartDate
                    &amp;&amp; 'Staff Absences'[Absence Start Date] &lt;= 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)

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

Forum Discussion

Networking Days based on Slicer or values in a table

2 Replies

Related content

network error

Network Protection Audit

Network navigator

GA availability for streaming connector virtual network

Wrap slicer values

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