cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Price Index

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!

 

 

10 REPLIES 10
Highlighted
Anonymous
Not applicable

Re: Price Index

Hi there.

 

Interesting problem 🙂 Would you, please, set up a small but faithful model in a PowerBI or Excel file and send it over? I'll take a look at this. By the way, the file, or accompanying file (Excel), needs to have an example of WHAT IT IS YOU REALLY WANT (I mean the correct output).

 

Then I'll be able to give you a solution. You can place a link to the file(s) on your OneDrive on GoogleDrive for me to take a look. 

 

Thanks.

 

Best

Darek

New Member

Re: Price Index

This one continues...  Great response from you! I have a similar dynamic cost history case.
I want to show history prices comparable to todays prices, I have a cost history index with 2 rows per year.


So, I have current costs calculated per each case, and want to present history case costs as a price-corrected measure.

My problem is that cost history index table has only a few rows, I should be able to reach to the latest previous row, pick up the multiplicator and multiply the found cost item with the latest previous index  (e.g. this row 100€ * matching nearest previous index value)

I got this working with manual year filter, but dynamic... @Anonymous ?

Highlighted
New Member

Re: Price Index

as simple as it goes

Cost Items

costitem

price

date

 

table with  cost history index, 
indexvalue (100 as base on 2010, growing by percents)

date (twice a year) , e.g. 1.1.2010 or 1.6.2010


Highlighted
Frequent Visitor

Re: Price Index

Anyone able to find a solution to this? 

 

Problem is; food prices like cherry only have at summer times and when we go winter price index fells like truck but it should not. Because it has to not count cheery anymore in the basket

 

June Lemon 5

June Chery 7

June Bread 2

June Total_Basket is 14  (food price index here is 100)

 

July Lemon 6

July Chery 8

July Bread 2

July Total_Basket is 16   (so food price index icreased %14 to 114)

 

Agu Lemon 3

Agu Chery 4

Agu Bread 2

Agu Total_Basket is 9   (so food price index decreased -%35 to 65)

 

No more cherry price in sept.

 

Sep Lemon 3

Sep Bread 2

Sep Total_Basket is 5   (so food price index have not changed. both lemon and bread is 2 so result should be %0 change with 65)

But dax calculates this as -%64 change to 36)

 

So we need a dynamix basket total calculator and add products like cheery to if it has both at start and end of calculation dates else should ignore that value from both start and end.

Thanks everyone.

Highlighted
Anonymous
Not applicable

Re: Price Index

The "basket price indexes" solution can be found in the attached file. It works exactly as you wanted. Please inspect the solution thoroughly.

 

Best

D

Highlighted
Anonymous
Not applicable

Re: Price Index

By the way, in order to get the price indexes, you have to click on any of the rows in the Base Basket table.

If you want to see the percentage difference (instead of the value of the index), you have to modify the measure a bit. Just subtract its value from 100% (which means 1).

Best
D
Highlighted
Frequent Visitor

Re: Price Index

Thank you very much darlove.

Can we may be make it hard coded to "January 2020 or first month items's price appears" to 100 so that we dont need to click a month year to see index? 

By this way I can achieve

yearly index,

-12 month index

5 year index

 

etc.

 

Thanks a bunch!

Highlighted
Anonymous
Not applicable

Re: Price Index

Here's the solution to the very first task by @gh_de. File attached.

 

Best

D

Highlighted
Anonymous
Not applicable

Re: Price Index

Hard-coding values into measures is very seldom a good idea. You want to make things responsive and dynamic. If you want to set a specific value for a dimension/measure, please use Filters on the View tab.

Best
D

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors