Return Value from Tbl2 where Tbl1.Value is between two Tbl2.Values

[PBI_Inquisitor]

Hi

I have two tables T1 & T2

T1 has

CustomerID	MonthID	Indicator from T2
12345	202301	0
23456	202301	1
34567	202301	0 (Returns 0 as 202301 is NOT between the start and end date in T2

The Indicator From T2 should return when the T1.MonthID is between the start and end months in T2

T2:

CustomerID	StartMonth	EndMonth	Indicator
12345	202301	202302	0
12345	202302	202304	0
12345	202303	202305	0
23456	202301	202302	1
34567	202302	202304	1

Tried various thing and either get #ERROR or no values returned

Any advice on best approach please?

Thanks in advance

P

[tamerj1]

Hi @PBI_Inquisitor 

I assume you can build one to one relationship between the two tables. Then you can create a new calculated column in T1

Indicator from T2 =
IF (
T1[MonthID] >= RELATED ( T2[StartMonth] )

&& T1[MonthID] <= RELATED ( T2[EndMonth] ),

1,

0

)

[PBI_Inquisitor]

Thanks for your reply @tamerj1 but unfortunately I cant build a 1:1 relationship in this instance - I've updated the Table 2 to show this

[tamerj1 · Accepted solution]

@PBI_Inquisitor 
The use

Indicator from T2 = 
VAR CurrentCusomer = T1[CustomerID]
VAR CurrentMonth = T1[MonthID]
VAR T2Start = CALCULATE ( MAX ( T2[StartMonth] ), T2[CustomerID] = CurrentCusomer )
VAR T2End = CALCULATE ( MAX ( T2[EndMonth] ), T2[CustomerID] = CurrentCusomer )
RETURN
    IF ( CurrentMonth >= T2Start && CurrentMonth <= T2End, 1, 0 )

[PBI_Inquisitor]

Thank you so much for your help @tamerj1 - much appreciated