How to identify multiple dates in 1 table to multiple pairs of dates in a 2nd table with conditions

[dmgebauer]

I have two tables, 'TeeTimes' and 'Closures' with data that I would like to compare.

The "Budget Tee Date" in the 'TeeTime' table to see if it falls between the 'Closure' tables "Close Date" and "Open Date". Then put the value of "Closure Type" from the 'Closure' table into the "Open/Close" column of the 'TeeTimes' table or simply the text value "Open" if no conflicts with the closures.

Department is a common field in both tables. 

Table views

Relationship

Thanks in advance!

[Greg_Deckler · Accepted solution]

@dmgebauer Maybe:

Open/Close Column =
  VAR __Dept = [Dept]
  VAR __Course = [Course]
  VAR __BudgetTeeDate = [Budget Tee Date]
  VAR __Table = FILTER(ALL('Closures'), __BudgetTeeDate >= [Close Date] && __BudgetTeeDate <= [Open Date])
  VAR __ClosureType = MAXX(__Table, [Closure Type])
  VAR __Result = IF( __ClosureType = BLANK(), "Open", __ClosureType)
RETURN
  __Result

  
  

[dmgebauer]

@Greg_Deckler  Thank you!!

[dmgebauer]

Close! but I will need to have the result based on the matching departments by each specific closure date range. Current formula seems to be applying a closure to all departments not the matching department.

[dmgebauer]

Looks like it is working with this update

Open/Closed =

  VAR __Dept = [Dept]

  VAR __Course = [CourseName]

  VAR __BudgetTeeDate = [Budget Tee Date]

  VAR __Table = FILTER(ALL('Closures'), __Dept = Closures[Dept] && __BudgetTeeDate >= [Close Date] && __BudgetTeeDate <= [Open Date])

  VAR __ClosureType = MAXX(__Table, [Closure Type])

  VAR __Result = IF( __ClosureType = BLANK(), "Open", __ClosureType)

RETURN

  __Result