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!

Hi v-kelly-msft , 

Thank you much, this is great!!

Now, I'm facing the problem with number of rows... I have 120 date values, excel table I sent you was just a sample.

I cant type as much rows manualy, and second thinkg, perhaps that number can increse.

So what I need is to always take last month and recalculate new one.  Is that possible?

Thanks, 

Alex

Hi  astojanac ,

As I said,if you need to do a multiply calculation in a loop,you'd better finish it outside desktop,such as SQL,then back to desktop to do a sum calculation.

Otherwise,it would be a large calculation in desktop as I show you in the last reply.

Best Regards,
Kelly

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