Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
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
@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.
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])
@Shelley in contract table you will have one row for each customer or there can be many rows for each customer?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2kone customer ID could have one record in the contract table or many records. It depends how much business we've done with them.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |