Get last change in price history table

[gleblanc]

Hi all,

I am once again asking for your wisdom.

I have one table (only one, with no relationship) showing the price history of products we buy.

The columns are:

IDHistory___IDProduct___ChangeDate___NewPrice
1__________AAA_______2012-01-01____100.00$
2__________AAA_______2012-02-22____110.00$
3__________BBB_______2012-03-01____300.00$
4__________BBB_______2012-04-01____320.00$
5__________CCC_______2012-04-01____500.00$

I need a new calculated column showing for each row the last price change entered for each product; therefore the last price from the vendor for that specific product.

By example, for product AAA, I want to see:

IDHistory___IDProduct___ChangeDate___NewPrice___LastPrice
1__________AAA_______2012-01-01____100.00$____110.00$
2__________AAA_______2012-02-22____110.00$____110.00$

How can I do it ? I'm pretty sure it's simple enough.

Thank you!

 

[ruve1k]

There is probably a better way to do this but for now this should work.

=SUMX(FILTER(Table1,
             Table1[ChangeDate]=MAXX(FILTER(Table1,
                                            Table1[IDProduct]=EARLIER(Table1[IDProduct],1)),
                                     Table1[ChangeDate]) 
          && Table1[IDProduct]=EARLIER(Table1[IDProduct])),
     [NewPrice])