Create a calculated column to identify rows that match a set of criteria based on other tables?

[Anonymous]

I have a problem I've been trying to work through in PBI and I could use the input / advice of those more experienced. I will lay out the tables / data and then summarize the problem below.

I have several tables:

• The first one is "Comm:SAPR", and each row represents a unique sale for what we will define as "Company A".

• The next is "Agreements", and each row represents an agreement that a customer (Company B) has signed with Company A. This agreement states that Company B has the right to buy a certain quantity (Quantity) of a certain product (Product) at a certain price (Price) from Company A indefinitely, so long as Company B makes an order for Product A and submits a claim for a rebate against the agreement every 6 months. If Company B fails to submit a claim against the agreement over a span of 6 months, the agreement is void. Otherwise, the agreement will automatically be voided when Company B has submitted claims up to the agreed upon Quantity.

• The next is "Claims", and each row represents a claim for a rebate that Company B has submitted against an agreement.

This is how they are connected:

Not pictured here are two separate lookup tables: "Customers" for customer info and "Products" for product info.

I have one last table, "Contract Calcs" that represents only agreements that are active and reports their start date, most recent claim date, expected end date, and other useful metrics.

Finally, here is what I am trying to do: As sales refresh, I am trying to identify new sales in the Comm:SAPR table that are liable to one of these rebate claims. So far, I have built these calculated columns in the Comm:SAPR table that identify if the customer on that SAPR record has an active agreement, as well as if the product on that SAPR record has an active agreement:

What I need to do is find a way to identify surely that a particular record is liable to a rebate claim. Are there any functions I am missing that would help me solve this problem?

Right now, I'm working on creating another indicator in the SAPR table that indicates if a particular SAPR record falls between the dates of active agreements. Then, I will build a calculated column that checks if all three indicators are true, and maybe build some further logic that verifies confidently that a particular record is liable to a rebate claim.

I know this is rather difficult to spell out in text, but are there any useful functions / approaches that I can look into to make this easier?

[Anonymous]

@Anonymous 

I guess you should re-post a question with a sample pbix including random data value. This kind of text description is just diffcult to understand.

Read this post to describe your sample:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards
Paul