Amortization Schedule cumulative for all leases is not able to work. It is working when I filter on each lease ID. I have a base table which all the lease ID, date(monthly), rent, interest, and present value. Sample Data Attached Sample Lease Data and snapshot My Approach ( Attached is the PBIX file for reference) Lease PBIX Sample File1. Brought the date dimension 2. Linked with Sample Data (Date) with Date Dimension (Date) 3. Create a derived/measure (Amortization) table based onLease Contracts Table Mesure Amortization Table Calculated Column - Period =GENERATESERIES ( 1, 36, 1 ) which has following measures. Measures 2. 3. 4. 5. What is Happening The cumulative is not working when I select all lease id(slicer) and all period(slicer), even when I slicer based on periods it does not work. Please see the screen shot below. However, when I filter on Lease ID, it showed us the right values except the total of Payment and Principal, but on rows value it is showing right. What is required (Please see attached file for reference) Lease Required Report this is the cumulative amortization schedule I ma trying to acheive in Power BI along with Date parameters 1. Opening balance = (all leases on Jan 1 2019 [117423 ]) - Lease 35 and Lease 38 (13210 each) because please see period 1,2,3,4

Forum Discussion

Advocate I

5 years ago

Solved

Amortization Cumulative Schedule DAX and Power BI Help

Amortization Schedule cumulative for all leases is not able to work. It is working when I filter on each lease ID. I have a base table which all the lease ID, date(monthly), rent, interest, and present value.

Sample Data Attached Sample Lease Data and snapshot

My Approach ( Attached is the PBIX file for reference) Lease PBIX Sample File
1. Brought the date dimension
2. Linked with Sample Data (Date) with Date Dimension (Date)
3. Create a derived/measure (Amortization) table based on

Lease Contracts Table
Mesure

Amortization Table
Calculated Column - Period =GENERATESERIES ( 1, 36, 1 ) which has following measures.

Measures

What is Happening
The cumulative is not working when I select all lease id(slicer) and all period(slicer), even when I slicer based on periods it does not work. Please see the screen shot below.

However, when I filter on Lease ID, it showed us the right values except the total of Payment and Principal, but on rows value it is showing right.

What is required (Please see attached file for reference) Lease Required Report

this is the cumulative amortization schedule I ma trying to acheive in Power BI along with Date parameters
1. Opening balance = (all leases on Jan 1 2019 [117423 ]) - Lease 35 and Lease 38 (13210 each) because
please see period 1,2,3,4

Need Help

v-janeyg-msft

5 years ago

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.

v-janeyg-msft
5 years ago
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.

14 Replies

amitchandak
Super User
5 years ago
farooqk_aziz , Refer If my video on this can help . Not excat , what you are looking for

https://www.youtube.com/watch?v=9VYqbj2h4zE

File at : https://community.powerbi.com/t5/Quick-Measures-Gallery/Financial-Magic-to-continue-with-10-Recently-added-Function/td-p/1233163
farooqk_aziz
Advocate I
5 years ago
sorry that's not the solution, even though your work is great. My requirement is different
farooqk_aziz
Advocate I
5 years ago
can someone help please
v-janeyg-msft
Community Support
5 years ago
Hi, farooqk_aziz

It’s my pleasure to answer for you.
According to your description,do you want the total row to calculate the cumulative value of the values in all the columns, I think I probably know how to do it, and know why you have the incorrect problems, but this is a long story, mainly because your measure is not rigorous and will change with context changes. I can also modify it,but the problem is, I don’t know which measures need to change, and which measures need to calculate as fixed values.
Could you share your desired result and your calculation logic?So we can help you soon.
You can use functions such as all,allselected( table) in measures to get more accurate values, and then use the form of sumx(summraize(), [ ]) to calculate the cumulative value.
like this:
PV = CALCULATE(SUM('Lease Contracts'[Present Value]),ALL('Lease Contracts'))
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.

farooqk_aziz

Advocate I

5 years ago

v-janeyg-msft please check your community inbox, as I am not able to post it here. It is keep giving error while posting.

v-janeyg-msft

Community Support

5 years ago

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.

farooqk_aziz
Advocate I
5 years ago
the cumulative beginning balance suppose to be 91003 which is the sum of all beginning balance for all the leases except 35 and 38 as both Leases 35 and 38 started at April 2019(Period 4) and March 2019 (Period 3) respecitively, and they added according to it. Lease 35 value is 13210 (April 2019, period 4), and Lease 38 is 13210(March 2019, period 3). Please see the table below , so Lease 38 total Present value (13210) will add in March(Period 3) of Amortization Beg Balance, and Lease 35(13210) will add in April 2014 (period 4) as per your forumula is picking in period 1 for lease 35 and 38 which is not correct. I tried to do it, but couldn't. your help will be appreciated it.
microsoft amortization
please see period 1,2,3,4

so these two lease supposed to be adjusted in amortization schedule in beginning balance by adding to period period 3 and period 4 during maortization balance (please see the table below). please help. I really appreciate your last effort

farooqk_aziz
Advocate I
5 years ago
@v-janeyg-msft Amortization Cumulative Schedule DAX and Power BI Help based on your previous solution it is working if any lease has same rental payment for all the, but it's facing an issue when rental payment changes every year for any lease. As per the screenshot below, whenever the rental payment changes it doesn't take the beginning balance from previousending balance. I have attached the link of PBIX sample file based on your previous solution. Can you please help? PBIX Sample File Link - One Drive
- v-janeyg-msft
  Community Support
  5 years ago
  Hi, farooqk_aziz
  
  I took a long vacation due to illness. This post is too long to reply anymore.
  
  I think you need to open a new post and explain all your problems now to get help.Thanks.
  
  Best Regards,
  
  Community Support Team _ Janey
Anonymous
Not applicable
5 years ago
v-janeyg-msft Hello! Can you please upload your solution again?

Thanks!

Forum Discussion

Amortization Cumulative Schedule DAX and Power BI Help

14 Replies

Related content

Amortization Schedule Help

Cumulative Total

Dax logic for Amortization Calculation

Creating amortization schedule for prepayments, accrual and deferred revenue

loan amortization?