Forum Discussion
RJS22
1 year agoNew Member
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....
- 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 ...
sevenhills
Super User
1 year agoI 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 ...