I'm assuming you need this calculation to work even if there are multiple rows next to each other with null signature dates. With that assumption, I think the following table calc is what you're looking for. Also see the attached workbook.
With these table calc settings:
I added an extra row to your data to test multiple null rows next to each other, and here are the results:
See attached workbook.
The calculation looks deceptively simple, but the logic behind it is a bit technical. Table calcs cannot keep stepping backwards in the partition conditionally. E.g. you can't say "for this row in the partition, use the first row before it for which some condition is true". Instead, they must know how many steps they're going backwards ahead of time. In this case, for each row of each partition we've written the table calc such that it examines the current row and the row immediately before it.
The calculation works by using a known property of the previous row (an invariant) to calculate the current row. By assuming that our calculation works as intended, we can assert the following invariant for the previous row:
[Expected Value Calc] = [Amount] - [Amount (prior non-null signature date)]
We can then rearrange that invariant to determine that, for the previous row:
[Amount (prior non-null signature date)] = [Amount] - [Expected Value Calc]
So if the previous row is null, We can evaluate [Amount] - [Expected Value Calc] for that row to figure out what the [Amount] is for the last row whose signature date is not null. That's what this part of the calculation is doing:
LOOKUP(ATTR([Amount]), -1) - PREVIOUS_VALUE(0)
And the rest of the calculation is built around that.
Hope that helps!
diff-non-null.twbx 20.7 KB