I have two dataframes, sales table & threshold table. Party A has to split revenue with Party B if the sales reach a certain threshold in either USD/AUD. This split is set by the threshold below, and can be changed anytime. For example, below shows a threshold change effective on 12 Dec 2025.
| Effective date | revenue threshold (USD) | revenue threshold (AUD) | Party A Split% | Party B Split% |
|---|---|---|---|---|
| 1-1-2024 | 100 | 50% | 50% | |
| 1-1-2024 | 200 | 80% | 20% | |
| 12-12-2025 | 100 | 150 | 50% | 50% |
| 12-12-2025 | 300 | 70% | 30% | |
| 12-12-2025 | 300 | 450 | 90% | 10% |
On sales table below, how can I get the resulting calculated columns “Party A Split US$” and “Party B Split US$” in Python? (btw, each sales are recorded in both USD & AUD using that day’s FX rate. Rmb, threshold applies if either currency hits it.)
| Sales_date | Invoice# | revenue (USD) | revenue (AUD) | Party A Split US$ | Party B Split US$ |
|---|---|---|---|---|---|
| 6-6-2024 | 001 | $10 | $15 | $10 (No split to B, no threshold met) | 0 |
| 6-6-2024 | 002 | $100 | $149 | $50 (50%) | $50 (50%) |
| 6-6-2024 | 003 | $400 | $599 | $320 (80%) | $80 (20%) |
| 7-7-2025 | 004 | $99 | $149 | $99 (No split to B, no threshold met) | 0 |
| 7-7-2025 | 005 | $200 | $301 | $140(70%) | $60 (30%) |
| 7-7-2025 | 006 | $301 | $440 | $270.9 (90%) | $30.1 (10%) |