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

[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”!