How to link two tables on date "between" 2 dates

[Tybaal]

Hi all, 

I'd like to link 2 tables on dates with a "between" relation.

I have a "ContractDate" in table 1 and a "ValidDateFrom" and "ValidDateTo" in table 2.

My goal is to create a relation like that :

1.ContractDate BETWEEN 2.ValidDateFrom AND 2.ValidDateTo

Is there a way to do that?

Thank you in advance.

[amitchandak]

@Tybaal , try like

calculate(countrows(Table1), filter(Table,table1[ContractDate] >= min(table2[ValidDateFrom] && table1[ContractDate] >=table2[ValidDateTo])), values(Table[contract_id]))

or

calculate(countx(values(Table[contract_id]), Table[contract_id]), filter(Table,table1[ContractDate] >= min(table2[ValidDateFrom] && table1[ContractDate] >=table2[ValidDateTo])))

[Tybaal]

Thank you @amitchandak, 

My issue is that I have no ID to link both tables... I only have the dates...

[V-pazhen-msft · Accepted solution]

@Tybaal 

There is no such between relationship in power bi, you can just link either the fromdate and todate with the ContractDate in manage relationship, and set the conditions with dax in later calculation.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.