I have a problem I am trying to solve. Basically, I am trying to build a report to identify variances between sales order lines and trade agreements with customers. There are two tables, a sales line table and a trade agreement table.
Sales order line table:
| Sales Order Number | Customer Account Number | Sales Line Price | Item Number | Quantity Ordered | Delivery Date |
Trade agreement table:
| Item Number | Customer Account Number | Quantity From | Quantity To | From Date | To Date | Price |
Some important factors:
- We often sell the same item number to multiple different customer numbers; if an item is sold to a specific customer, the customer number on the trade agreement table will say all, if not, it will list the item number
- The trade agreement from date - to date are the dates a trade agreement is active; a blank to date means the trade agreement is ongoing and not scheduled to end
- The trade agreement quantity from - quantity to are the order quantities a trade agreement applies to, and if there are multiple agreements within the same dates with different quantities, that means there is a price break; a from quantity of 1 and a blank to quantity means the agreement applies to orders of all quantities and there is no price break
- Important to note, the from quantity is inclusive and the to field is exclusive, so an item with agreements of 15 to 30 and 30 to 45, and an order quantity of 30, would fall into the 30 to 45 trade agreement, not the 15 to 30 agreement
So there are a few different examples/scenarios I am trying to address with this report:
- The sales line price is different than the price of a trade agreement that falls within the window of a delivery date:
- Item number 1, customer number 2000, sales order 150, a quantity of 100 ordered, a sales price of $30, due to be delivered 1/15/2024
- There is a trade agreement for the item that applies to the customer account number all that is active within the window of the delivery date, but the price on the trade agreement ($25) does not match the price on the sales order line
Sales order line table:
| Sales Order Number | Customer Account Number | Sales Line Price | Item Number | Quantity Ordered | Delivery Date |
| 150 | 2000 | 30 | 1 | 100 | 1/15/2024 |
Trade agreement table:
| Item Number | Customer Account Number | Quantity From | Quantity To | From Date | To Date | Price |
| 1 | All | 50 | 150 | 12/1/2023 | 1/31/2024 | 25 |
- There is no trade agreement in place for an item when that item is due to be delivered:
- Item number 2, customer number 1500, sales order 500, a quantity of 1,200 ordered, price of 30, due to be delivered 1/10/2024
- There are trade agreements for this item that apply to the customer account number all, but they all ended before the delivery date, so there is no trade agreement for the item
Sales order line table:
| Sales Order Number | Customer Account Number | Sales Line Price | Item Number | Quantity Ordered | Delivery Date |
| 500 | 1500 | 30 | 2 | 1,200 | 1/10/2024 |
Trade agreement table:
| Item Number | Customer Account Number | Quantity From | Quantity To | From Date | To Date | Price |
| 2 | All | 1000 | 2000 | 10/1/2023 | 12/31/2023 | 30 |
- There is no trade agreement in place for the quantity ordered
- Item number 3, sales order 1000, customer number 100, order quantity of 5, sales price of 50, due to be delivered 1/3/2024
- There is a trade agreement for the customer account number all, but the quantity applies to order quantities of 15 to 29, and the sales line order quantity was 5, so there is no valid trade agreement for this item
Sales order line table:
| Sales Order Number | Customer Account Number | Sales Line Price | Item Number | Quantity Ordered | Delivery Date |
| 1000 | 100 | 50 | 3 | 5 | 1/3/2024 |
Trade agreement table:
| Item Number | Customer Account Number | Quantity From | Quantity To | From Date | To Date | Price |
| 3 | All | 15 | 29 | 10/1/2023 | 1/31/2024 | 50 |
So basically, the variances are:
- When the price on a sales order line is different then the price on a trade agreement
- There is no active trade agreement in place when an item is due to be delivered
- There are trade agreements in place for an item when it is due to be delivered, but the quantities on the agreement are different than the quantity ordered
I want to first look for a trade agreement for the item number and the specific item number, so for item 1 and customer account number 2000, I would first look to see if there is an active trade agreement for that item and customer account number 2000, but if there wasn't one, but there was a valid agreement for the customer account number all, then there would be no variance.
Any ideas on how to go about accomplishing this in Power BI?
