Find if a date is within multiple periods

[YuvalD]

Hi,

I am looking for a way to evaluate if a date of a visit at customer falls within a period that the customer was active. 
I have the following structure:

VisitData
CustomerID	VisitDate	Result
1	15/01/2022	Yes
1	16/05/2022	Yes
1	01/02/2022	No
2	01/06/2022	Yes
3	01/12/2022	No

CustomerHistory
CustomerID	Active Start	Active End
1	01/01/2022	30/01/2022
1	01/05/2022	30/05/2022
2	01/05/2022
3	01/06/2022	30/11/2022

So for each visitdate, I want to check in Customer History table and get a result. 

For example -

CustomerID #1 has 3 visits, and 2 periods of activity. only two visits are valid and one is not in any valid period.

CustomerID #2 has 1 visit and 1 period which is not over yet (No End date)

CustomerID #3 has 1 visit and 1 period and the visit is out of the period therefore not valid. 

How can I calculate the 'Result' Field considering there are multiple possible periods for each customer ?

Many thanks !!

[YuvalD]

Hi @FreemanZ,
That worked perfectly. 
Thanks !

[FreemanZ · Accepted solution]

hi @YuvalD 

try to add a column in the visit table with this:

Result2 = 
VAR _customer = [CustomerID]
VAR _date = [VisitDate]
VAR _count =
COUNTROWS(
    FILTER(
        CustomerHistory,
        CustomerHistory[CustomerID] = _customer
            &&CustomerHistory[ActiveStart]<=_date
            &&(CustomerHistory[ActiveEnd]>=_date||CustomerHistory[ActiveEnd]=BLANK())
    )
)
RETURN
IF(  _count>=1, "Yes", "No")

i tried and it worked like this:

p.s. no relationship is needed.