Data Modelling for varying product price across time


Hello Sam,

We have products for which we do price increase from time to time based on the cost.
When have to compare the sales revenue from a product(s) this year Vs last year with two different prices - How to set up the data model

Where will suggest to setup the pricing table - Lookup table or the fact table.

Following is the data model that I use to get the comparison of revenue


Please note I have kept the pricing table both in lookup (AOPSBPPriceLookup) and fact table (AOPSBPPrice)

The Power BI report looks as shown below,

Do you have any videos that can help !


Check out this detailed tutorial

It walks through the exact technique to use for this. There’s a little bit to do with the model setup and then using the appropriate DAX formulas.



Thanks Sam.

Will write to you if I have any further questions.