Greetings,
I am trying to calculate the sum of how much we buy from a supplier during different rebate. The point is, if we go over a certain amount during a period, we pay less.
My problem is I do not have a direct link to my stuff bought to my rebates.
Let's start with the relevant tables
The rebate is caracterised by its ID, the SupplierID, DateStart, DateEnd, TurnoverMin and RebateRate. Others are details for now.
It is important to note, that you can have multiple rebates, with the exact same supplier and the exact same dates. I still need to calculate them, I cannot just take the maximum date. We can have multiple rebate from the same supplier if we reach certain treshold. So we can have 1% if we buy more than 100.000€ ot 3% if we buy more than 1.000.000€ over the current year. This end up creating Line of rebate with the exact same dates and exact same supplier. The difference is that the 1st has a turnover min of 100.000€ and rebateRate of 1 while the other has a turniver min of 1.000.000€ and a rebateRate of 2 (since the old system that was using it was adding them together. So it add all the rebates that apply. If we get over 1.000.000 we also satisfy 100.000 for a total rebate of 1+2 = 3)
Supplier is just the info of the supplier. It ends up being to top of the informations but all we really care about is its ID. None of its other infos are relevant to our calculation.
Pieds_A is the invoice. It has all the details other than the products and values for each invoice. For what matters (I think) we only care about its supplierID, and its date.
Lignes_A is every line of what we bought. For each line, it's a product, a quantity and their price.
To make things easier, I already made a calculated column in Pieds_A that gets the sum of all the lines its linked to (named NetValue). This allows us to ignore Lignes_A but I am nonetheless pointing it here in case it matters.
How can I, for each rebate, find all the Invoices that have the same supplier (shouldn't be complicated, they're both linked to it) and happened during the period of the rebate (so Pieds_a[Date] is between rebate[dateStart] and rebate[DateEnd]) and sum the Pieds_a[NetValue] for each rebate. Then, from there on, create a new rebated value depending on the sum of all rebate that apply to each invoice.
Now, this is the whole process I'm trying to go through, the real problem I have is the 1st part, find how much we pay during each rebate. i'll try to figure out myself how to apply the rebates next, but I spent two days trying to get these values, my brain start hurting. I hate not doing it myself, but at some point, I'm wasting too much time for something I am not getting anywhere.
Thank you for your help and have a nice day.
Edit: Here's an exemple with values. In green are what is available, and in orange is what I would like to calculate. In the invoice, I have to calculate the rebate rate applied to the invoice, depending on which ones they are part of and which min turnover have been reached. Then I take that rate as a percentage off of the values.
In the rebate rate, I have to evaluate how much We spent on these supplier during that period. If I reach teh treshold (rebate rate) I flag it as being reached.
As I said in my explanation, the value in supplier don't matter as they don't impact this calcul in any form, they're just a matter of hierarchy. And for Lignes_a (where the total is actually located) I already created a new column in Pieds_a with the total, to save having to check one more relationship.
