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 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

Need Help

2 Replies

  • 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?