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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
GINMED
Helper III
Helper III

Opening balance in amortization schedule

Hi, 

please help me to find correct way to calculate correct Opening balance = Previous period Closing balance.

There is my expectation:

 

GINMED_0-1682583375766.png

 

There is my formula to calculate Opening Balance: 

OPEN_BAL =
VAR _PV = [PV]
var _Rate = MAX('Lig'[Rate in period])
var _Period = MAX('Lig_Project'[Seq])
var _AllPeriod = CALCULATE(max(('Lig_Project'[Seq])),ALLSELECTED('Lig_Project'),VALUES('Lig_Project'[PL Nr.]))
var _PMT = PMT(_Rate,_AllPeriod,_PV,0)

return
fv(_Rate,_Period-1,_PMT,_PV,0)*-1
 
But it doesn't give me correct result! Because there is specific issue with first interest payment, wich is correct amount given in table, and others Interest should be calculated.
GINMED_1-1682583857991.png

 

Please, sombody help me to solve this issue!
There is my Power Bi sample
4 REPLIES 4
lbendlin
Super User
Super User

the link to the PBI file is private. Please check.

Hi, @lbendlin 

there is my sample data

lbendlin_0-1683030357744.png

There is no link between Lig and Lig_Project.  Therefore PV will be blank.

 

@lbendlin 

it doesn't affect to calculation. Lig_project is projected table from table Lig.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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