Forum Discussion

RJS22's avatar
RJS22
New Member
1 year ago
Solved

Networking Days based on Slicer or values in a table

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....
  • sevenhills's avatar
    1 year ago

    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 ...