Help with previous() and peek() to determine new calculated values
Assume the following situation where some users have a budget to spend, and an increase of their budget after some time. Now the user can spend some of his budget or not, but we need to determine their current budget after a purchase and/or increase.
An example is given in the image. Specifically the green highlighted section is not working properly at this moment.
For each month I determine the current starting budget of that specific month, and the final budget at the end of the month. Hence columns L and M.
The problem I'm facing is that the MonthsEndBudget is not working properly in displaying the data in the first record, i.e. 90 for UserId 1 in jan 2020. And second, when there is a budget increase, column F orange highlighted, is not added to the MonthsEndBudget in january 2021. At which we also see an increment of +1 in column G (light blue).
The not functioning properly formula used to determine MonthsEndBudget is: If(peek(BudgetId,-1) = BudgetId,If(IsNull(TransactionAmount),Peek('MonthsEndBudget'),CurrentBudget-TransactionAmount)) As MonthsEndBudget
And to determine MonthsStartBudget:
If(BudgetId = Previous(BudgetId), If(IsNull(TransactionAmount), Peek(MonthsEndBudget),Previous(MonthsEndBudget)), 0) as MonthsStartBudget
Can someone guide me into the right direction to determine the MonthsStartBudget and MonthsEndBudget properly, as displayed in the image?