Connections between tables using date intervals

[qmestu]

Hi,

I have a dataset in which i need to connect two tables using date fields. However, these are intervals, such as "START_DATE" and "END_DATE". A simplified example would be:

table 1:

start_date	end_date	value
1/1/2022	1/6/2022	A
31/6/2022	31/12/2022	B

table 2:

start_date	end_date	correspondent_value
1/3/2022	1/4/2022	X
31/9/2022	30/11/2022	Y

Choosing a slicer with the A value would result in the corresponding value being X. How can i connect these two tables?

Thanks.

[v-jianboli-msft · Accepted solution]

Hi @qmestu ,

Please try:

Apply the measure to the visual level's filter:

Measure = IF(SELECTEDVALUE('Table 2'[start_date])>=MIN('Table 1'[start_date])&&SELECTEDVALUE('Table 2'[end_date])<=MAX('Table 1'[end_date]),1)

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

There be dragons. What should happen when multiple intervals from Table 2 match your selection from table 1?  What if the use selects multiple entries from table 1?

Please consider all possible scenarios and then provide sample data that fully covers them all.