Forum Discussion

rafamorim87's avatar
rafamorim87
Frequent Visitor
2 years ago

Build a table

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 falli...
Need Help
Jihwan_Kim's avatar
Jihwan_Kim
Super User
2 years ago

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's avatar
rafamorim87
Frequent Visitor
2 years ago

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?