Compare Date in one table to multiple rows in another table

[Steven_Phillips]

I have what I thought would be an easy comparison but I just have gotten stuck for some reason.... I have 2 tables, a "Work Orders" table and an "Agreements" table..... I'm needing to compare whether the account that the work order is on had an agreement at the time that the work order was completed.

Work Order Table
WO Completion	WO#	Acct #	Had Agreement?
1/12/2022	100	100	No
8/1/2023	103	100	Yes
6/8/2023	101	200	Yes
7/1/2023	102	300	Yes
4/1/2024	104	300	No
8/1/2024	105	300	Yes

Agreement Table
Agreement Number	Agreement Start Date	Agreement End Date	Acct #
1000	7/1/2022	6/30/2023	100
1000-1	7/1/2023	6/30/2024	100
1000-2	7/1/2024	6/30/2025	100
2000	3/1/2023	2/29/2024	200
2000-1	3/1/2024	2/28/2025	200
3000	1/1/2023	12/31/2023	300
3001	6/1/2024	5/31/2025	300

The field in blue is pretty much what I'm trying to get. I essentially just need it to look at the date on the first table and compare it to the second table to determine if the completion date falls between the agreement start and end date based on the acct #.  You'll notice with account number 300, there was a gap where they did not have agreement from 01/01/2024 through 05/31/2024 so I need to be able to account for that.

[mickey64 · Accepted solution]

Step 0: I use your DATA below.

Step 1: I unpivot the 'Agreement' table on Power Query Editor.

- Before -

- After -

Step 2: I add a 'Agreement Flag' column to the 'Agreement' table on Power Query Editor.

Step 3: I add a Conditional Column named 'Date Cal' to the 'Agreement' table on Power Query Editor.

Step 4: I make 2 tables and add some relationships on Power BI Desktop. (Date:yyyy/mm/dd)

    Calendar = CALENDAR("2020/1/1","2030/12/31")

    Acct No = SUMMARIZE('Agreement','Agreement'[Acct #])

Step 5: I make 2 measures below.

    M_Agreement Flag = CALCULATE(SUM(Agreement[Agreement Flag]),

        FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

    M_Flag = IF('Agreement'[M_Agreement Flag]>0,"Yes","No")

Step 6: I add 2 columns to the 'Work Order' table on Power BI Desktop. 

    Agreement Flag = CALCULATE([M_Agreement Flag],

        KEEPFILTERS(ALLEXCEPT('Work Order','Work Order'[WO Completion],'Work Order'[Acct #])))

    Agreement Flag2 = CALCULATE([M_Flag],

        KEEPFILTERS(ALLEXCEPT('Work Order','Work Order'[WO Completion],'Work Order'[Acct #])))