Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Price difference from first to last payment

I'm trying to create a calculated column that will calculate the difference in price between the first time they bought it and the last time they bought it. The blue column is what I'm looking to do. For example, Item 1 was $28.84 the first time it was bought and $35.01 the last time, for a difference of $6.17. Item 2 was $77.53 the first and last time it was bought, so the difference is $0.

 

Invoice DateItemPricePrice Creep
10/27/2022Item 1$35.01$6.17
3/11/2022Item 1$26.02$6.17
2/23/2022Item 1$28.84$6.17
2/23/2022Item 1$28.84$6.17
11/18/2022Item 2$77.53$0.00
7/8/2022Item 2$60.88$0.00
6/30/2022Item 2$77.53$0.00
5/31/2022Item 2$72.33$0.00
5/2/2022Item 2$77.53$0.00
11/21/2022Item 3$60.88$2.54
9/8/2022Item 3$60.88$2.54
6/13/2022Item 3$68.06$2.54
6/10/2022Item 3$72.33$2.54
4/11/2022Item 3$58.34$2.54
Need Help

1 Reply

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    This calculated column formula works

    Column = LOOKUPVALUE(Data[Price],Data[Invoice Date],CALCULATE(max(Data[Invoice Date]),FILTER(Data,Data[Item]=EARLIER(Data[Item]))),Data[Item],Data[Item])-LOOKUPVALUE(Data[Price],Data[Invoice Date],CALCULATE(min(Data[Invoice Date]),FILTER(Data,Data[Item]=EARLIER(Data[Item]))),Data[Item],Data[Item])

    Hope this helps.