Summarize: Dynamic Amortization Schedule 07-22-2022 08:57 AM

[farooqk_aziz]

Amortization Schedule 

In today's world, accountant and financial analyst has to maintain an excel sheet and formulas to create ammortization scheudle. However, Power BI platform with good Power BI developer to make this process automated. 

In this Report, I have utilized monthly rental payment and Interest Dsicount for each month to calculate the amortization.

I have utilize some of the DAX functionalities to make this process faster and automated. 

Summarize function 

All 

Amortization formula in DAX form 

 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
                        )

Please check out attached pbix, and laso if you need shot me a message at BI49@frq49.onmicrosoft.com

eyJrIjoiNThjNDRhMWUtNDFiNC00MmQyLWI3MzktN2FjZDlmYTcyZjg1IiwidCI6IjQzMDE5M2U1LTk1OGEtNDMxNC05MDk2LWIzMWIxMGYyOTk2YiIsImMiOjl9

[jledyard88]

I just sent you an email. I have a very similiar problem but can't quite get your model/example to work for me. 

[Icad01]

Thanks for sharing 🙂

You may want to simplify the data model, by merging the header table inside the details table.

Take a look at:
https://www.sqlbi.com/articles/header-detail-vs-star-schema-models-in-tabular-and-power-bi/