Dynamic Product Pricing that changes each year

Hello!

I have a data model with a simple transaction table with skus, units sold, and quantity on hand (QOH). I also have a SKU dimension table which lists more info about the skus - price, description etc. The two tables are connected with a one to many relationship based on the SKU field.

Each year, the price of the different skus changes. There are 2 price columns on the DimSkus table - one for the price in 2021 and a second for the price in 2022.

When slicing and dicing the data, how can I tell Power BI to grab the price for the right year when looking back at historical data.

For example, if the user selects a slicer for 2021, I want calculated COGS by multiplying the price on the SKU table for 2021 times the quantity sold on the transaction table for all transactions that took place in 2021. For transactions that take place in 2022, I want the data model to grab the updated prices for 2022 (which are all 1 USD more than the price in 2021), and multiple units sold by the 2022 price.

Hopefully, this makes sense. I’m unsure how to tell Power BI to first check the year of sale on the transaction table, and then grab the price from that year on the dimskus table.

I attached a PBIX file with a simple data model. I believe I need to tie everything to a date table, but I’m unsure how to structure this.

Thanks!
Pete

DataModelSample.pbix (284.4 KB)

Found the solution on enterprisedna.com youtube channel:

1 Like