Price Index

[gh_de]

Hi there, 

we would love to have price indices in power bi - has anybody tried that before and could share best practices? 

we have three tables

1. Price History
   date - sku - price 
   has an entry for each sku whenever a price changes
2. Transactions
   date - sku - order value
   contains transactional data for each sku whenever there is revenue
3. and a date table which is connected to both

in order to have a (dynamic) price index, we would like to be able to select two dates - reference date (where index is defined as 100) and the compare date, for which the index 110 says: prices are 10% higher than on reference date.

the challenge is, that the price history table does not contain an entry for every date (which would bloat the table with 12000 skus to 4,400,000 rows), so we need measures, which contain the last prices on *or before* the reference date and the compare date. does anybody have a measure which caclulates an index on those? 

the 2nd challenge is to create a weighted price_index which weights the index with the order value on reference date or compare date. 

thanks in advance!