cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BI49
Helper I
Helper 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, @BI49 

 

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

13 REPLIES 13
Phogon
Helper II
Helper II

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

 

Thanks!

BI49
Helper I
Helper 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, @BI49 

 

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

 

 

BI49
Helper I
Helper 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, @BI49 

 

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.

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, @BI49 

 

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.

BI49
Helper I
Helper I

can someone help please

BI49
Helper I
Helper I

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

amitchandak
Super User
Super User

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors