Calculate Unavailability hours in a shift and exclude overlapping entries

[PKCRonny]

Hi Community,

I have a calculation that works out actual hours in a shift minus any unavailabilty, but require it to exclude any that overlap and not be calculated twice. There are 2 tables; 1 to show all employees and the shift they are rostered for each day.  The 2nd table is a list of all unavailability recorded.  Each unavailabilty has been split onto individual days.

Emp	Date	Shift Start	Shift End	Shift Hours
1	24/06/2024	08:00	14:00	6
1	25/06/2025	-	-	0

Emp	Date	Unavailabilty Type	Unavailabilty Start	Unavailabilty End
1	24/06/2024	Annual Leave	08:00	14:00
1	24/06/2024	Sickness	00:00	23:59
1	25/06/2024	Sickness	00:00	23:59

Added a column to the shift table to work out unavailable hours, it calculates -12 for the 24th giving an actual hours total of -6

[Anonymous]

Hi @PKCRonny 

Ensure there's a relationship between your two tables based on to accurately correlate shifts with unavailability periods.

 Use DAX to calculate the overlapping hours between the shift hours and the unavailability periods.

This involves creating a new measure that compares the start and end times of shifts and unavailability periods to determine the overlap duration.

Adjust your calculation for actual hours to subtract only the unique unavailability hours from the total shift hours.

For example:

Actual Hours = 
SUMX(
    ShiftsTable,
    [Shift Hours] - 
    MAXX(
        FILTER(
            UnavailabilityTable,
            UnavailabilityTable[EmpDate] = ShiftsTable[EmpDate]
        ),
        [Unavailability Duration]
    )
)

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PKCRonny]

hi, thanks for that.

Wondered if I could add another scenario, as I did not include some of the harder entries by the staff.
There is occasions where both unavailability have to be included, so would be looking to see if any of the availability overlapped.   Adding different entries to the original table for new scenario.

Emp	Date	Unavailabilty Type	Unavailabilty Start	Unavailabilty End
1	24/06/2024	Meeting	10:00	11:00
1	24/06/2024	Private Appointment	13:00	15:00

for this scenario, both unavailability would be included.

[PKCRonny]

Unavailable Hrs (column) =

var availTimeStart = CONVERT(factAvailability[startTime],DATETIME)

var availTimeEnd = CONVERT(factAvailability[endTime],DATETIME)

var shStart1 = factAvailability[startShift1]

var shEnd1 = factAvailability[endShift1]

var shStart2 = factAvailability[startShift2]

var shEnd2 = factAvailability[endShift2]

var chkShift1 = IF(factAvailability[availabilityType]="BUSY" && availTimeStart <= shEnd1 && shStart1 <= availTimeEnd, 1, 0)

var chkShift2 = IF(factAvailability[availabilityType]="BUSY" && availTimeStart <= shEnd2 && shStart2 <= availTimeEnd, 1, 0)

var _availMins1 = IF(chkShift1=1,  DATEDIFF( IF(shStart1<=availTimeStart, availTimeStart, shStart1)

, IF(shEnd1<=availTimeEnd, IF(FORMAT(shEnd1,"HH:mm")="00:00", shEnd1+1, shEnd1), availTimeEnd), MINUTE), 0)

var _availMins2 = IF(chkShift2=1,  DATEDIFF( IF(shStart2<=availTimeStart, availTimeStart, shStart2)

, IF(shEnd2<=availTimeEnd, IF(FORMAT(shEnd2,"HH:mm")="00:00", shEnd2+1, shEnd2), availTimeEnd), MINUTE), 0)

var returnValue = (_availMins1 + _availMins2) / 60

RETURN returnValue