Customer based pricing with date based changes

DAX DAX Calculations
1

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.

Irene

2

Could you provide some images as reference of the customer based pricing?

For exchange rate you could check out this tutorial by Sam

3

Hi , we’ve noticed that no response has been received from you since the 27th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!