Build a table

[rafamorim87]

Goodnight
Could someone help me, please?

I have to build a table so that the first column is equal to the result of the last column of the previous row.
I tried to create this but I ended up falling into redundancy.

The last column is the result of the value of the first column + second column - third column.

ex:
Beginning of February (= January Balance) + February Credit - February Debit = February Balance

Beginning of March (= February Balance) + March Credit - March Debit = March Balance

So far ahead

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

WINDOW function (DAX) - DAX | Microsoft Learn

Credit: = 
SUM( Data[Credit] )

Debit: = 
SUM( Data[Debit] )

Opening balance: = 
VAR _t =
    ADDCOLUMNS (
        ALL ( 'Calendar'[Year-Month sort], 'Calendar'[Year-Month] ),
        "@amount", CALCULATE ( SUM ( Data[Amount] ) ),
        "@credit", CALCULATE ( SUM ( Data[Credit] ) ),
        "@debit", CALCULATE ( SUM ( Data[Debit] ) )
    )
VAR _startyearmonth =
    MINX ( FILTER ( _t, [@amount] <> BLANK () ), 'Calendar'[Year-Month sort] )
VAR _startamount =
    CALCULATE (
        SUM ( Data[Amount] ),
        'Calendar'[Year-Month sort] = _startyearmonth
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Calendar'[Year-Month sort] ) = _startyearmonth,
        _startamount,
        SUMX (
            WINDOW ( 1, ABS, -1, REL, _t, ORDERBY ( 'Calendar'[Year-Month sort], ASC ) ),
            [@amount] + [@credit] - [@debit]
        )
    )

Closing balance: = 
VAR _t =
    ADDCOLUMNS (
        ALL ( 'Calendar'[Year-Month sort], 'Calendar'[Year-Month] ),
        "@amount", CALCULATE ( SUM ( Data[Amount] ) ),
        "@credit", CALCULATE ( SUM ( Data[Credit] ) ),
        "@debit", CALCULATE ( SUM ( Data[Debit] ) )
    )
RETURN
    SUMX (
        WINDOW ( 1, ABS, 0, REL, _t, ORDERBY ( 'Calendar'[Year-Month sort], ASC ) ),
        [@amount] + [@credit] - [@debit]
    )

[rafamorim87]

Good morning
Thank you very much for the information.

I placed a photo of my semantic model below.

I had the following doubt:

You suggested doing:

Opening balance: =
VAR_t =
ADDCOLUMNS (
ALL ( 'Calendar'[Year-Month sort], 'Calendar'[Year-Month] ),
"@amount", CALCULATE ( SUM ( Data[Amount] ) ),
"@credit", CALCULATE ( SUM ( Data[Credit] ) ),
"@debit", CALCULATE ( SUM ( Data[Debit] ) )

But I don't have an Amount column in my database, so I can't do (SUM ( Data[Amount] )

In my database , Data[Credit] = Lancamento_Entradas[Valor], Data[Debit] = Lancamento_Saidas[Valor], but

Amount in my model is a measurement (Amount = [Saldo Inicial Planejado]+[Receita Planejada]-[DespesaPlanejada])

How could I do it in this case?