Hey AirTable brains trust,
I’m trying to build a balance tracker in AirTable so that I can not only set up a budget, but also forecast what future account balances might be.
I feel like I’m SO CLOSE. But I’ve found 2 flaws:

the formula only seems to work if there’s an entry for the day before it. If there are no transactions for a few days, then the formula breaks somehow.

the formula can’t cope if there are two transactions on the same day.
I suspect the problem is found in the DateAmt column / formula (but that’s mostly an educated guess).
I’ve straight copied the formula from the base that I’ve modelled it off (created by @W_Vann_Hall, of course). Here’s the base:
And here’s the formula:
(VALUE(DATETIME_FORMAT(Date,'X'))/86400)&
":"&
Amount&
IF(
FIND(".",Amount&"")=0,
".00",
REPT("0",7LEN(Amount&""))
)
According to the column description, the original formula (which I’ve modelled this off) “Concatenates 5digit ‘days since epoch’ value of Date + “:” + Price to create dayspecific persistent price record. Adds decimal and trailing zeroes if needed to ensure price portion of string is 7 characters long.”
But I have no idea what that means or how to change it, so that it works the way I want.
Here’s a link to my attempt so you can see the shambles for yourself:
I’ve based this on what I’ve learned (with my admittedly limited understanding) from @W_Vann_Hall’s responses to these 3 posts, which are based on similar concepts:

Multirecord calculations (c.f. ‘running balance,’ ‘row number,’ and the like) (a lot of the explanation in this thread was so technical that it went over my head, which could be the problem!)
Any ideas of where I’ve gone wrong or how to fix it would be DEEPLY appreciated! It feels like there’s something fundamental that I’m missing here.