Calculate difference from latest value

[gdssiqueira]

Hi guys,

this one is a variation of this post. Since I can't mark it as 'unsolved', it made sense to start a new topic.

In the previous post, I have an example, in which I wanted to subtract two values from different rows when they had a different type (there's a Type column) and referred to the same date. Now I'm going to present a different example because the goal is a little different.

Here's the simplified version of my dataset. The Selection column actually represents a few columns grouped into one.

The table is not necessarily ordered by latest date.

Date Type Value Selection

1/6    A      4           X

1/5    A      2           X

1/4    B      3           X

1/3    A      5           Y

1/3    B      2           Y

1/2    B      3           X

1/1    A     3           Y

1/1   B      1           Y

What I want is, for every row of type A, calculate the difference between its value and the value of the last row of type B, with a Date equal or prior to its Date and of the same Selection. I'll try to show the output I'm expecting:

Date Type Value Selection  Difference

1/6    A       4          X             4 - 3 =1 (from row 3)

1/5    A       2          X             2 - 3 = -1  (from row 3)

1/4    B       3          X             blank

1/3    A       5          Y             5 - 3 = 2 (from row 6, because row 5 has different Selection value)

1/3    B       2          X             blank

1/2    B       3          Y            blank

1/1    A       3         Y            3-1 = 2

1/1    B      1          Y            blank

How could I approach this? Aditionally, if there's another column with a true/false value saying if you should ignore that row, how would that change my approach? Feel free to message me with questions if something isn't clear enough.

Thanks ! 🙂