Forum Discussion

Shelley's avatar
Shelley
Icon for Post Prodigy rankPost Prodigy
6 years ago

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

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 

 

 

Need Help

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

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

    • Shelley's avatar
      Shelley
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Shelley
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        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])
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    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.