Give suggested values conditioned on two fields

[TrangNN]

Hi, I have 2 tables, 1 is Room, 1 is Event. I want to give a list of available room whenever a new Event row is added with a specified date such that total duration of room used in that date does not exceed 13. 

I manage to find events with similar dates but cannot sum up duration of events happening in the same room because I haven't figured out a way to sum duration of rows having the same room ID. Thus, I cannot have suggested values.

I would appreciate your help!

[Marc_Dillon]

Perhaps something like this:

FILTER(
  room ,
  COUNT(FILTER(
    event,
    AND(
      [room_id] = [_THISROW-1].[id] ,
      [date] = [_THISROW].[date]
    )
  ) ) < 14
)