How to Lookup a Maximum Value in another table without counting the current value

[Shelley]

Hi All, I'm struggling tremendously with this. I have a table with orders for new contracts. Data looks like this:

order date / contract number / customer ID / order value

1/1/2019 / 123 / ABC / $100

3/2/2019 / 456 / SAM / $250

Then I have another table with contract data like this:

contract number / customer ID / start date / end date

123 / ABC / 1/15/2019 / 1/14/2020

675 / ABC / 1/10/2018 / 1/10/2019

235 / Fred / 1/23/2018 / 1/23/2019

I'm trying to determine if a new order is a new customer contract or a renewal. So I want to see if there is a contract end date in the contract table for the same customer ID that is prior to the current contract. If so, this is a renewal. If not, this is new. How do I lookup a maximum end date in the contract table, without counting the brand new contract?

Any help is appreciated.

@Greg_Deckler 

[Ashish_Mathur]

Hi,

In the new_orders table, write this calculated column formula

=if(CALCULATE(Max(all_contracts[End date]),FILTER(all_contracts,all_contracts[Customer ID]=EARLIER(new_orders[Customer ID])&&all_contracts[Contract number]<>EARLIER(new_orders[Contract number])))>new_orders[Order date],"New","Renewal")

Hope this helps.

[amitchandak]

Please find the pbix.

https://www.dropbox.com/s/xfmkfak3ic5a3cc/Order_contract.pbix?dl=0

As of now, I have used the Start date <= order date.  Because of sample data is not sufficient. But End Date <= Order date can also be used.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Shelley]

@amitchandak Thank you for the help. I really appreaciate it; however, my company security doesn't allow me to review the file you placed on DropBox, so I cannot see your solution.

Incidentally, the other challenge with this is the new contract start date or order date can be BEFORE the last contract end date. Perhaps this doesn't matter in the case of your solution, but it is something that I have also struggled with in trying to find a solution.

[amitchandak]

Please find the formula I created. For this condition, I will check and get back.

Old Contact Date= maxx(FILTER(contract,contract[customer ID]='Order'[customer ID] && 'Order'[contract number]<> contract[contract number]  && contract[start date]<='Order'[order date]),contract[end date])

Old Contact No = maxx(FILTER(contract,contract[customer ID]='Order'[customer ID] &&  contract[start date]<='Order'[Old Contact]),contract[contract number])

[parry2k]

@Shelley  in contract table you will have one row for each customer or there can be many rows for each customer?

[Shelley]

@parry2kone customer ID could have one record in the contract table or many records. It depends how much business we've done with them.