!I am trying to compare actual purchased amount over selected period of time compared to base period amount. Where base period amount is the price going into the selected period applied to the quantity during the period.
What is the actual result?
I got the firstsortedvalue to work but fails when we have 2 or more different prices on the same date for the same item and facility. This is due to multiple suppliers delivering the same item.
What I want?
I want to get a weighted avg price for those multiple suppliers for that date and item. This avg price I can then use to multiply on the Quantity for the selected period.
In the case where there are 2 or more prices I need the inner function to calculate the weighted price.
Firstsortedvalue(Distinct
Price1*Quantity1+Price2*Quantity2
/
(Quantity1+Quantity2)
In my example you will see 3 formulas,
Purchase value EUR = Just the spend for the period
Purchased value base period EUR = Quantity for the period * Price closest before Startdate (or closest after Startdate if there is no purchasee 12 months before startdate)
Savings EUR = Purchase value EUR - Purchased value base period EUR
