Forum Discussion

Henk50's avatar
Henk50
Regular Visitor
4 years ago
Solved

Checking if date falls between 2 dates on different table

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            ...
  • Jos_Woolley's avatar
    Jos_Woolley
    4 years ago

    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

Jos_Woolley's avatar
Jos_Woolley
Icon for Solution Sage rankSolution Sage
4 years ago

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