Take previous value in a column and subtract with the next value in the same column

[Patrick]

I have one column with a daily record of a person’s weight. I want to create a new virtual column that calculates the difference between the previous day weight and the current day weight to see the daily weight loss.

I tried to use the following calculation in a new virtual column labeled: Weight Loss and added the following formula: ([_THISROW_BEFORE].[Weight] - [_THISROW].[Weight]). The column type is decimal

I saved the changes but I get a 0.00 value result in the column of the table.

I checked the essentials documentation and searched online but can’t find an answer.

What am I overlooking? Thank you in advance for your help.

[Marc_Dillon · Accepted solution]

Patrick:

in a new virtual column… ([_THISROW_BEFORE].[Weight] - [_THISROW].[Weight])

Not going to work.

A “BEFORE” value is only going to be available during an edit, or immediately afterwards (like in a Bot). A Virtual Column will be getting re-evaluated upon every sync or edit of the record. Even if it did get the right value briefly, it would quickly be lost.

You may be able to record the difference in a Real column, possibly even with the exact same expression.

Otherwise, your options would be to add a child Table to hold all historical weight values, or add a new column to hold just the single previous weight value.

[Patrick]

Hi Mark,

Thank you for your help with sharing this insight. I will create a separate column that contains the previous weight value and then calculate the delta within the same row record.

Patrick

[Eduardo_Sampaio]

Hi Patrick. How did you accomplish this solution? Could you share?