Hi, I’m slowly learning a lot here at Enterprise DNA and have gotten a long way so far! I’m so glad I’ve found this resource. It has saved my hide many times, so thank you so much!!
I’ve watched the video on calculating revenue with prices that change per quarter, however I can’t work out how to make the same principle work for me.
We have customer based pricing as opposed to product pricing. We increase the price per customer every 2 or 3 years. How can I calculate sales revenue over the years using the correct price?
My pricing is in a separate table. At this stage there are two records per customer with a price and the effective date on each. eg: Customer A has a price of $2.50 per click effective from 01-Oct-14 and a second record of $2.75 effective from 01-Apr-17. There will be more records in the future as the price changes.
I have a Total Sales measure which is clicks * price, but when comparing previous years how do I choose the right price depending on what date the sale was made? If I want to forecast revenue with a future increase how do I do that?
Further complexity is introduced as pricing is in different currencies for different customers, but I need to report in GBP. Exchange rates do not change every day, but we do update them every now and then. So the formula needs to look up the correct price for the sales date and then apply the most current exchange rate.
Hoping someone can help me out here.
Thanks in advance.