Calculated Column based on row diff

[Anonymous]

Hello,

I have a custom dataset as shown below and I am looking for a calculated column called "Price change" which do the following operation,

For the same material, look for the valid_to date and if it is the first valid date then price change is 0 else diff of the price.

Material      valid_from        valid_to           price     Pricechange
16558          5/20/2018        10/20/2018      12           0
16558          10/21/2018     12/21/2018      10          -2
16558          12/22/2018      12/31/9999      14          4
16559          6/21/2018        10/22/2018      12         0
16559          10/23/2018      12/31/9999       10        -2

Thanks in advance.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula work

=if(ISBLANK(LOOKUPVALUE(Data[Price],[Material],Data[Material],Data[Valid_from],CALCULATE(MAX(Data[Valid_from]),FILTER(Data,Data[Material]=EARLIER(Data[Material])&&Data[Valid_from]<EARLIER(Data[Valid_from]))))),0,[Price]-LOOKUPVALUE(Data[Price],[Material],Data[Material],Data[Valid_from],CALCULATE(MAX(Data[Valid_from]),FILTER(Data,Data[Material]=EARLIER(Data[Material])&&Data[Valid_from]<EARLIER(Data[Valid_from])))))

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula work

=if(ISBLANK(LOOKUPVALUE(Data[Price],[Material],Data[Material],Data[Valid_from],CALCULATE(MAX(Data[Valid_from]),FILTER(Data,Data[Material]=EARLIER(Data[Material])&&Data[Valid_from]<EARLIER(Data[Valid_from]))))),0,[Price]-LOOKUPVALUE(Data[Price],[Material],Data[Material],Data[Valid_from],CALCULATE(MAX(Data[Valid_from]),FILTER(Data,Data[Material]=EARLIER(Data[Material])&&Data[Valid_from]<EARLIER(Data[Valid_from])))))

Hope this helps.

[Anonymous · Accepted solution]

Thank you very much...It worked like a charm.

[Ashish_Mathur]

You are welcome.