Forum Discussion
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 | 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?
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
2 Replies
- Jos_Woolley
Solution Sage
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
Thank you! this works and i can follow the calculations!
