Normaly I trac all my consuption in a GSheet, but how do I substract the ald consumtion from the new one ?
Hello, @Robert_K, welcome to the community!
There is thread about how to do calculations with other rows.
However, you have one record for each month, I would prefer just filter previous month’s value:
This formula finds previous month by calling thisRow.Date.RelativeDate(-1).DateTimeTruncate("month") and then runs Filter() for all rows in the table to select a row where its Date.DateTimeTruncate("month") is equal to the previous month of the current row.
Then formula extracts Value field. The result is still a list, not a single value (despite in this case there is a list with one value). So, we need to call First() to get first element. If this list was empty, First() returns Blank, so we need an additional check and substitution with IfBlank(). I assume there is default value of 0 if no previous month present, but you can replace 0 by Value to make Difference = 0 in this case.
Then this all calculations should be subtracted from thisRow.Value to get a difference.
P.S. I’ve also added conditional format to exclude months duplicates.
2 Likes