Amortization Cumulative Schedule DAX and Power BI Help

[farooqk_aziz]

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

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

[v-janeyg-msft · Accepted solution]

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.

[Anonymous]

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

Thanks!

[farooqk_aziz]

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

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]

@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 · Accepted solution]

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.

[AndresFromeroS]

Hello Janey,

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

Thanks

[farooqk_aziz]

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

[v-janeyg-msft · Accepted solution]

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.

[farooqk_aziz]

@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 

[farooqk_aziz]

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

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]

can someone help please

[farooqk_aziz]

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

[amitchandak]

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