Amortization Cumulative Schedule DAX and Power BI Help

Hi, farooqk_aziz 

Due to the nature of work, we only respond to forum posts.

In view of your larger needs, I try my best to perfect your needs. Your method isn't very good which causes many problems,and I will give my ideas here.

You can sort each id by date(calculate column) to get the period, and then calculate what you want in a summarize table, so that the total can be automatically kept correct.

Like this:

period = 
RANKX (
    FILTER (
        ALL ( 'Lease Contracts' ),
        [LeaseID] = EARLIER ( 'Lease Contracts'[LeaseID] )
    ),
    [Rent Date],
    ,
    ASC
)

Table =
ADDCOLUMNS (
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE (
                'Lease Contracts',
                [period],
                [LeaseID],
                [Rent Date],
                [Present Value],
                [Rent],
                "Beginning balance",
                    VAR PV =
                        CALCULATE (
                            SUM ( 'Lease Contracts'[Present Value] ),
                            FILTER (
                                ALL ( 'Lease Contracts' ),
                                [LeaseID] = SELECTEDVALUE ( 'Lease Contracts'[LeaseID] )
                            )
                        )
                    VAR I = 0.0033
                    VAR Series =
                        SELECTEDVALUE ( 'Lease Contracts'[period] )
                    VAR Payment =
                        SELECTEDVALUE ( 'Lease Contracts'[Rent] )
                    VAR Result =
                        IF (
                            PV
                                * POWER ( 1 + I, Series - 1 )
                                - Payment
                                    * DIVIDE ( POWER ( 1 + I, Series - 1 ) - 1, I ) >= 0,
                            PV
                                * POWER ( 1 + I, Series - 1 )
                                - Payment
                                    * DIVIDE ( POWER ( 1 + I, Series - 1 ) - 1, I ),
                            0
                        )
                    RETURN
                        Result
            ),
            "Interest", [Beginning balance] * 0.0033
        ),
        "Ending balance",
            IF (
                [Beginning balance] - ( [Rent] - [Interest] ) >= 0,
                [Beginning Balance] - ( [Rent] - [Interest] ),
                0
            )
    ),
    "Principal",
        IF ( [Rent] - [Interest] >= 0, [Rent] - [Interest], 0 )
)

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello, @BI49

What I wrote to you before has perfectly solved your first problem, and there is no problem with the data. If you want to see the balance based on the date, simply create a table visual without period and 😊😊😊

Like this:

Best regards

Janey Guo

If this post helps,then consider Accepting it as the solution to help other members find it faster.