Creating an INDEX Rate

[mateus_luzzi]

Hello!

I'm new to PBI and having lot of doubts, mostly of DAX...

I need to create an index chart, but I cant manage to create a DAX formula 

Basically I have a fact (item price) that can change by dimensions (store, date)

Let me give you an example:

Product Name	Store	Price	Date
Banana	Shop One	10	11-23-2020
Banana	Shop Two	8	11-23-2020
Apple	Shop One	5	11-23-2020
Apple	Shop Three	6	11-23-2020

So, this is our index basis

I need the minimum price of the items (Banana = 8, Apple = 5) on that day

Then I need to take the average (8+5/2 = 6.5)

Then this average (since is the first) is my index basis (100%)

Then I'll need to get the data for the next day, and so on

Ex next day:

Product Name	Store	Price	Date
Banana	Shop One	9	11-24-2020
Banana	Shop Two	11	11-24-2020
Apple	Shop One	5	11-24-2020
Apple	Shop Three	5	11-24-2020

The minimum price are (Banana = 9, Apple = 5)

The average is (9+5/2=7)

So my index for this day is (today's avg min price) / (yesterday's avg min price)

7/6.5 = 1.076 = +7.6% is the index for day 02

I tried to summarize but I didnt work "the expression refers to multiple columns... cannot be converted to scalar"

Thanks!

[mateus_luzzi]

Hi @Ashish_Mathur @parry2k  Thank you both for your replies! 

Yep, @Ashish_Mathur  , it's supposed to be 6.5 not 6. I corrected it, thanks!

@parry2k  What you've done is almost what I needed, but you cleared my mind and I've got what I needed!

I first calculated the average of min price, as Parry2k did, but categorized by product and sale date.

Preco Medio Por Produto Por Dia = CALCULATE(AVERAGEX( SUMMARIZE(Vendas,Vendas[Produto_Nome],Vendas[Data_Venda],"Preco",MIN(Vendas[Preco])),[Preco]),Vendas[Data_Venda]>=TODAY()-30)

Then, for displaying it as an index in a line chart

Indice X = IFERROR( [Preco Medio Por Produto Por Dia]/ CALCULATE([Preco Medio Por Produto Por Dia],PREVIOUSDAY('Calendário'[Date])) ,"")

And cumulated variation

Indice X Acumulado = IFERROR( VAR Datedif = -30+(TODAY()-LASTDATE('Calendário'[Date])) RETURN [Preco Medio Por Produto Por Dia]/ CALCULATE([Preco Medio Por Produto Por Dia],DATEADD('Calendário'[Date],Datedif,DAY)) ,"")

Then I simple plotted both measures on a line chart by date axis

Thank you, friends! Thank you all! Happy XMAS!

[Ashish_Mathur]

Hi,

Shouldn't the denominator in your last calculation be 6.5?  So the answer should be 7/6.5 = 7.69%.  Also, please share your calculations in an MS Excel workbook for a few more days.

[parry2k · Accepted solution]

@mateus_luzzi I have the solution attached and I believe that is what you are looking for. 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡