Need a simple calculation Currentvalue - previousvalue, cannot find a good method

[chrisB13]

Hi,

I am trying to do a very very simple calculation, and maybe I am overthinking this.   I did some research and I cannot seem to find a "simple" way to achieve this and what I have found it seems that other users are experiencing similar problems.   I know that this calculation is very easy in Excel

I need to calculate the most current value (based on date), and subtract the previous value (based on date)

Here is the data, I need the red value 38.64 - the blue value 21.47, then to the end of the list (blue 21.47 - orange 28.30) and so on. 

I have about 300 different symbols and this is just 1, so indexing created an issue 

The end goal of this is simply to determine if the most recent value is greater than the previous.   So I am open to suggestions on this.   I will need to conditionally format the cells "green" if its greater and "red" or no formating if its less than.  (I can take care of the formatting, just adding it for context)

Thank you in advance

[Anonymous · Accepted solution]

@chrisB13  - One way to do this is in Power Query. Try these steps:

0. If the different Symbols are in separate columns, you will need to first select the date and "Unpivot Other Columns".

1. Order by Symbol and Date.

2. Add an index column starting from 0, and another starting from 1.

3. Merge the table with itself, joining the one index column to the other. This way, you can retrieve the previous value.

4. Add a Custom Column that returns 0 if the current and previous Symbols are different, otherwise the current minus the previous value.

Hope this helps,

Nathan