DAX Help required

[cottrera]

Hi

I have a model with two tables.  My first Table = tenant table which has a Tenant number, property number and a tenant start date.

The example below shows two different tenants living in one property at different times 

Tenant#	Property#	Tenancy start date	Tenancy end  date
123	777	01/01/2020	02/03/2021
456	777	03/03/2021	01/08/2025

My second table contains all the repairs that have taken place in the propery.  I would like a DAX function that  determines if the repair logged date was within the teancy start and end date , in order to populate the new column on the repairs table  highlighted in red with the tenant number.  

Repairs#	Property#	Repair logged date	DAX = Tenant#
30010	777	28/03/2020	123
30861	777	17/05/2020	123
29087	777	18/05/2020	123
50673	777	22/06/2020	123
78367	777	28/07/2020	123
44331	777	04/02/2021	123
20546	777	11/02/2021	123
96152	777	13/02/2021	123
51452	777	21/02/2021	123
68519	777	22/04/2021	456
83781	777	08/01/2022	456
12385	777	29/01/2022	456
43231	777	26/08/2022	456

thank you 

Richard

[cottrera]

Hi Greg

thank you for your quick response the DAX function works fine 😀

[Greg_Deckler · Accepted solution]

@cottrera Maybe:

Tenant # Column =
  VAR __Property = [Property#
RETURN
  MAXX(FILTER('Tenant Table', [Property#] = __Property && [Repair logged date] >= [Tenancy start date] && [Repair logged date] <= [Tenancy end date]),[Tenant])