Dynamic calculated columns based on previous records

Hi  astojanac ,

Sorry I forgot to mention that since loop is not permitted in dax calculation, we only have a workaround to do a sum calculation,for multiply,there's no good solution expect hard coding.

Check my formula below:

For measure：

Measure =
VAR _mindate =
    MINX ( ALL ( 'Table' ), 'Table'[Date] )
VAR _previousinterest =
    CALCULATE (
        MAX ( 'Table'[Interest] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Column] = MAX ( 'Table'[Column] ) - 1 )
    )
VAR _opening =
    CALCULATE (
        MAX ( 'Table'[Opening] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _mindate )
    )
VAR _startinterest =
    CALCULATE (
        MAX ( 'Table'[Interest] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _mindate )
    )
VAR _line1 =
    _opening - MAX ( 'Table'[Payment] )
        + ( _opening - MAX ( 'Table'[Payment] ) ) * _startinterest
VAR _line2 =
    _line1 - MAX ( 'Table'[Payment] )
        + ( _line1 - MAX ( 'Table'[Payment] ) )
            * CALCULATE (
                MAX ( 'Table'[Interest] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Column] = 2 )
            )
VAR _line3 =
    _line2 - MAX ( 'Table'[Payment] )
        + ( _line2 - MAX ( 'Table'[Payment] ) )
            * CALCULATE (
                MAX ( 'Table'[Interest] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Column] = 3 )
            )
VAR _line4 =
    _line3 - MAX ( 'Table'[Payment] )
        + ( _line3 - MAX ( 'Table'[Payment] ) )
            * CALCULATE (
                MAX ( 'Table'[Interest] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Column] = 4 )
            )
VAR _line5 =
    _line4 - MAX ( 'Table'[Payment] )
        + ( _line4 - MAX ( 'Table'[Payment] ) )
            * CALCULATE (
                MAX ( 'Table'[Interest] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Column] = 5 )
            )
VAR _line6 =
    _line5 - MAX ( 'Table'[Payment] )
        + ( _line5 - MAX ( 'Table'[Payment] ) )
            * CALCULATE (
                MAX ( 'Table'[Interest] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Column] = 6 )
            )
RETURN
    SWITCH (
        MAX ( 'Table'[Column] ),
        1, _line1,
        2, _line2,
        3, _line3,
        4, _line4,
        5, _line5,
        6, _line6
    )

For column:

Column 2 =
VAR _mindate =
    MINX ( 'Table', 'Table'[Date] )
VAR _previousinterest =
    CALCULATE (
        MAX ( 'Table'[Interest] ),
        FILTER ( 'Table', 'Table'[Column] = EARLIER ( 'Table'[Column] ) - 1 )
    )
VAR _opening =
    CALCULATE (
        MAX ( 'Table'[Opening] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _mindate )
    )
VAR _startinterest =
    CALCULATE (
        MAX ( 'Table'[Interest] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _mindate )
    )
VAR _line1 = _opening - 'Table'[Payment] + ( _opening - 'Table'[Payment] ) * _startinterest
VAR _line2 =
    _line1 - 'Table'[Payment]
        + ( _line1 - 'Table'[Payment] )
            * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( 'Table', 'Table'[Column] = 2 ) )
VAR _line3 =
    _line2 - 'Table'[Payment]
        + ( _line2 - 'Table'[Payment] )
            * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( 'Table', 'Table'[Column] = 3 ) )
VAR _line4 =
    _line3 - 'Table'[Payment]
        + ( _line3 - 'Table'[Payment] )
            * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( 'Table', 'Table'[Column] = 4 ) )
VAR _line5 =
    _line4 - 'Table'[Payment]
        + ( _line4 - 'Table'[Payment] )
            * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( 'Table', 'Table'[Column] = 5 ) )
VAR _line6 =
    _line5 - 'Table'[Payment]
        + ( _line5 - 'Table'[Payment] )
            * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( 'Table', 'Table'[Column] = 6 ) )
RETURN
    SWITCH (
        'Table'[Column],
        1, _line1,
        2, _line2,
        3, _line3,
        4, _line4,
        5, _line5,
        6, _line6
    )

And you will see:

For the related .pbix file,pls see attached.

For loop calculation I would recommend below blogs:

https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314

https://community.powerbi.com/t5/Community-Blog/Previous-Value-Recursion-in-DAX/ba-p/638320

Hope the above would help.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!