I have 2 tables, one which contains all my people information the other table contains their sick days and reasons.
StaffAbsence contains the following
Absence Key, Date, PersonRef, Period
A People Table where the list repeats every period for the employees this is laid out as follows
AbsenceKey,PersRef,Period,StartDate,LeaveDate,FirstDateofPeriod,LastDayofPeriod
What I would like to do is count all sickness days from the staff absence table which are in the period. However there are some instances where the person has a logged days sickness after there leave date.
So I want to count all sickness days in the period unless there is a leave date then I want to count to the leave date or period end date whichever is sooner.
This is my dax but it doesn't seem to be registering the <= and appears that this is being overrules somewhere.
SickeDays =
VAR StartDate = MINX(STAFFPERIODIC,STAFFPERIODIC[First Date of Period])
VAR LeaveDate = MAXX(STAFFPERIODIC,STAFFPERIODIC[LEAVE_DATE])
VAR PeriodEnd = MAXX(STAFFPERIODIC,STAFFPERIODIC[Last Day of Period])
VAR MaxDate = IF(LeaveDate < PeriodEnd, LeaveDate, PeriodEnd)
VAR Result =
CALCULATE(
COUNTROWS(STAFFABSENCE),
FILTER(STAFFABSENCE,STAFFABSENCE[DATE] >= StartDate &&
STAFFABSENCE[DATE] <= MaxDate || LeaveDate = BLANK()
)
)
RETURN
Result
