Data Calculation

[Sadee]

I have the following 'Cashbook' table columns. How do i calculate the Opening and Closing Balance of each Date (i.e, 12-12-2023, 12-11-2023). Please help.

Transaction ID	Date	Type	Category	Description	Opening Balance	Cash Inflow	Cash Outflow	Closing Balance
	12-12-2023	Income
	12-12-2023	Expense
	12-12-2023
	12-12-2023
	12-11-2023
	12-11-2023

[AleksiAlkio]

To find the latest Closing balance, you would need to have one column that you can use to sort your rows. Or is the idea to have the same Opening and Closing balance vaues for each row as it sounds you have more than two rows for each day?

[Sadee]

Yes there will be multiple row entries for any specific date. 
Opening Balance = (previous day) Closing Balance
Closing Balance = Opening Balance + Cash Inflow - Cash Outflow

[AleksiAlkio]

The issue is that you would probably want the same Closing Balance in each of your rows from the same date. And if you want to have it as a normal column, you would need to count them everytime when a new record for that same date is added. Another way is to use a virtual column but it sounds you need a normal column. Is my assumption correct?

[JSO]

If it's any idea, I have a Bank Movements table where I'm interested in knowing the balance after each movement.
For this I use a MovCtas table and the columns:
IDEnt (Bank ID), of type Ref with another Banks table.
IDMov (Number) Initial value: COUNT(SELECT(MovCtas[IDMov], ([IDent] = [_THISROW].[IDENT])))+1
....
MovImporte (Decimal)
MovSaldo (Decimal) Initial value: LOOKUP([_THISROW].[IDent]&": "& NUMBER([_THISROW].[IDMov]-1),"MovCtas","_ComputedKey","MovSaldo") + [MovImporte]

_ComputedKey (Text) KEY and LABEL . App Formula: CONCATENATE([IDent],": ",[IDMov])

This allows me, when registering a record of an IDent, to present the balance of the last record of the same and add the MovImporte of this same record and save it in the table.

Perhaps this, with the pertinent changes, can help you.