Hi Power BI Genius,
I'm having difficulty creating a DAX measure to retrieve approximate values from the two tables provided below.
Using the average cost of products sold in Table 2, I aim to find the shipment number with the closest unit cost received and the most recent shipment date received (if the unit costs received are the same in multiple shipments) from Table 1.
Does anyone experience in creating something similar like this?
| Table 1 | | | |
| Shipment Number | Shipment Date Received | Product | Unit Cost Received |
| 1000 | 5/12/2023 | A | 5.83 |
| 1001 | 1/01/2024 | B | 10.13 |
| 1002 | 2/03/2024 | C | 18.43 |
| 1003 | 18/02/2024 | B | 10.54 |
| 1004 | 17/02/2024 | C | 18.23 |
| 1005 | 29/02/2024 | A | 5.83 |
| 1006 | 10/04/2024 | B | 10.45 |
| 1007 | 15/04/2024 | C | 18.39 |
| 1008 | 20/05/2024 | A | 5.8 |
| Table 2 | |
| Product | Average Cost sold |
| A | 5.825 |
| B | 10.5 |
| C | 18.23 |
Result wanted:
| | | |
| Product | Average Cost sold | Approximate Search for Shipment |
| A | 5.825 | 1005 |
| B | 10.5 | 1003 |
| C | 18.23 | 1004 |
