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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to make table formula with variables dynamic, by using selected value function

Hi Community,

 

I have made a loan amortization schedule using a table whith the formula shown below.

But I want to change the variable PV, r, and n to be dynamic using selected value function that will be picking up data from another table instead of entering the variables manualy in the table formula.

I have tried but with no luck I do not know what am I missing 

 

Amortization Schedule =
var PV = 240000
var r = 0.07/12
var n = 30*12
Var p = (r* PV)/(1 - POWER((1+r),-n))

var period =
SELECTCOLUMNS(
GENERATESERIES(1,360,1),
"Period", [Value]
)

var InitialSchedule =
ADDCOLUMNS(
Period,
"Beginning Balance", PV*POWER(1+r,[Period]-1) - p*DIVIDE(POWER(1+r,[Period]-1)-1,r),
"Monthly Payment", p
)

var RemainingSchedule =
ADDCOLUMNS(
InitialSchedule,
"Interest", [Beginning Balance]*r,
"Principal", [Monthly Payment]- ([Beginning Balance]*r),
"Ending Balance", [Beginning Balance]-([Monthly Payment]-([Beginning Balance]*r))
)

return
RemainingSchedule
2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

Anonymous
Not applicable

Thanks @amitchandak let me go through the pbix file throughly but I think this might do the trick.

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , I have created something similar - check if this pbix can help

https://www.dropbox.com/s/5yyx32ozwaw1ezc/10%20Financial%20Functions.pbix?dl=0

Anonymous
Not applicable

Thanks @amitchandak let me go through the pbix file throughly but I think this might do the trick.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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