Checking if date falls between 2 dates on different table

[Henk50]

Hi all,

I've found similar topics, but it's hard to applicate this on my case;

I have two tables:

Table Contracts

Supplier	Contract version	Contract start date	Contract end date
A	1	1-1-2022	31-1-2022
A	2	1-3-2022	1-4-2022
B	1	1-1-2022	31-12-2022

Table Invoices

Supplier	Invoice date	Calculated column; contracted supplier?
A	10-1-2022	Yes
A	10-2-2022	No
B	etc	etc

The calculated column; contracted supplier is the goal.

An invoice should be registered under contract, if the invoice date falls between the begin& start date of any contract of that supplier. so in above example; In the first row of the invoice table, the invoice date is 10-1-2022. 10-1-2022 is for supplier A, a date between a contract start and enddate. 10-2-2022 isn't.

With my little dax knowledge i know that i have to search the invoice date in the contractstable, whilst keeping the filter on the supplier of that invoice. But my head can't translate this into dax yet. Any ideas?

[Henk50]

Thank you! this works and i can follow the calculations!

[Jos_Woolley · Accepted solution]

Hi,

Calculated Column in Invoices table:

Contracted Supplier = 
VAR InvoiceDate = Invoices[Invoice date]
VAR Supplier = Invoices[Supplier]
RETURN
    IF(
        COUNTROWS(
            FILTER(
                Contracts,
                Contracts[Supplier] = Supplier
                    && Contracts[Contract start date] <= InvoiceDate
                    && Contracts[Contract end date] >= InvoiceDate
            )
        ),
        "Yes",
        "No"
    )

Regards