Initial and Closing Balances period by period in table with exception in first month

[hectornauta]

Greetings.
I need to know if the following is possible:
Let’s assume the following data structure (description could be account, client, type, whatever):
description (string), date (date), amount (float)

Display the initial and closing balances for each month in a table (month by month). The exception is that the first month will not be taken from the previous month (which doesn’t exist) but rather, for example, the sum of that month’s amount for a particular description (which we’ll assume only exists in the initial period).
For example, given the data:

description, date, amount
Initial Balance, 2025-01-01,1000
AAAA, 2025-01-01,100
BBBB, 2025-02-01,100
DDD, 2025-03-01,100

The resulting table should be:

Rows	2025-01-01	2025-01-01	2025-01-01	2025-02-01	2025-02-01	2025-02-01	2025-03-01	2025-03-01	2025-03-01
	Initial	Movements	Closing	Initial	Movements	Closing	Initial	Movements	Closing
	1000	100	1100	1100	100	1200	1200	100	1300

I’ve tried runningSum but can only get closing balances.
And testing with periodOverPeriodLastValue I have cyclical errors (since the closing balance is equal to the initial balance + movements, and the initial balance is the previous closing balance, the formulas somehow crash). The closes that I got was using periodOverPeriodLastValue in the Initial Balance and runningSum in the Closing Balance, but I could not get the special case of the first month of initial balance to work. Examples of my calculated fields (that does not solve the problem, but they may be useful or give context).

InitialBalance

coalesce
(
    periodOverPeriodLastValue(
        ClosingBalance,
        date,
        MONTH,
        1
    ),
    sumIf(
        amount,
        {description}='Initial Balance'
    )
)

ClosingBalance

runningSum(
    sum(amount),
    [truncDate("MM",date) ASC]
)

[Thomas]

Hi @hectornauta,

You might want to explore whether the lag function would help in your case.

The InitialBalance calculation would then look as follows:

coalesce
(
    lag(
     {Closing Balance},
     [{ date} ASC],
     1
)   ,
    sumIf(
        { amount},
        description='Initial Balance'
    )
)

And the Movements calculation might look like this:

sumOver(
    sumIf(
        { amount},
        description<>'Initial Balance'
    ),
    [truncDate("MM",{ date}) ASC]
)

Due to the limited sample data, I haven’t tested any edge cases, but the result with the calculated field definitions from above looks as follows:

Did this answer your question? If so, please help the community out by marking this answer as “Solution”!