Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
farooqk_aziz
Advocate I
Advocate I

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 

Smaple Snapshot.PNG

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
Data Model.PNG
Table Structure.PNG

Lease Contracts Table 
Mesure 
Total PV.PNG

Amortization Table
 Calculated Column - 
Period =GENERATESERIES ( 1361 ) which has following measures. 

Measures

 

Beginning Balance.PNG

2. 

Payment.PNG
3. 
Interest.PNG

 

 

4. 

Principal.PNG

 

5. 

Ending Balance.PNG

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. 
amortization cumulative snapshot.PNG

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. 
amortization visual snapshot lease id.PNG

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,4please see period 1,2,3,4


2 ACCEPTED SOLUTIONS

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.

View solution in original post

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:

3.png

Best regards

Janey Guo

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

View solution in original post

14 REPLIES 14
Anonymous
Not applicable

@v-janeyg-msft Hello! Can you please upload your solution again?

 

Thanks!

farooqk_aziz
Advocate I
Advocate I

@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 balanceI have attached the link of PBIX sample file based on your previous solution. Can you please help? PBIX Sample File Link - One Drive 

BI49_1-1629626943478.png

 

 

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

 

 

farooqk_aziz
Advocate I
Advocate I

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

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

Could you share your solution? please. The link is broken.

Thanks

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
microsoft amortization.PNGplease see period 1,2,3,4please 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

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:

3.png

Best regards

Janey Guo

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

@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 balanceI have attached the link of PBIX sample file based on your previous solution. Can you please help? PBIX Sample File Link - One Drive 

BI49_0-1629626596076.png

 

 

@v-janeyg-msft  thank you I modified your above query as per my actual model, and it works. Thank you for all your support. 

v-janeyg-msft
Community Support
Community Support

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

can someone help please

farooqk_aziz
Advocate I
Advocate I

sorry that's not the solution, even though your work is great. My requirement is different

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.