Forum Discussion

chundrub's avatar
chundrub
Frequent Visitor
1 year ago

Amortization Table Calculations

Hi Team , I have an query related to Power BI DAX where the user need a Lease sales payments where there is an Loan Amount=10500 interest rate=0.0076278 per Month Deferral Period for 6 months an...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, chundrub 

Read your requirements very carefully, but unfortunately, still can't understand what you ultimately want to calculate. I have a limited knowledge of economics and can't make it clear in your description what the initial data is, it seems to be 10,500 but don't see you using it, can you give all the data involved in manipulating this data as an example in one row as in the screenshot you post to help understand your requirements better. As well, some Measure is missing. Thanks for your understanding.

 

Best Regards,
Yang
Community Support Team

 

chundrub's avatar
chundrub
Frequent Visitor
1 year ago

yes the initial amount is 10500 and the value I hardcoded it get the  correct when I'im passing for values manually 
took the sumx values as to get the calculated values in row wise

Lease Payment = SUMX(VALUES('Calendar'[Month-Year]),[LP Base])

Compound1sumx = SUMX(VALUES('Calendar'[Month-Year]),[Compound_base1])

Period Year =
VAR LeaseStartDate = SELECTEDVALUE('Lease Entry'[Beginning Date])
VAR CurrentDate = SELECTEDVALUE('Calendar'[Start of Month])
VAR FiscalYearStartMonth = 5 // May

// Adjust LeaseStartDate and CurrentDate to fiscal year basis
VAR LeaseYear = YEAR(LeaseStartDate)
VAR CurrentYear = YEAR(CurrentDate)
VAR CurrentMonth = MONTH(CurrentDate)

// Adjust the start date based on fiscal year
VAR AdjustedLeaseStart =
    IF(MONTH(LeaseStartDate) < FiscalYearStartMonth, LeaseYear - 1, LeaseYear)

// Adjust the current date based on fiscal year
VAR AdjustedCurrentYear =
    IF(CurrentMonth < FiscalYearStartMonth, CurrentYear - 1, CurrentYear)

// Calculate the fiscal period difference in months
VAR FiscalMonthsBetween =
    DATEDIFF(DATE(AdjustedLeaseStart, FiscalYearStartMonth, 1), DATE(AdjustedCurrentYear, FiscalYearStartMonth, 1), YEAR)

RETURN
FiscalMonthsBetween

In Net Investment Measure I have added Period year 
In measure above last paremeter is hardcoded beacuse when th add the value at the end only i can get correct value that year so it dynamically pick value from APR month of the fiscal year to calculate for rest of the year 

 


Interest 10787.40*0.76278=82.28    Principal redcution =100-82.28=20.22   next month value=10787.40-20.22=10767.18

unlees i pass the value 10787.40 th next year is not populating if  take the vale from net investment itself is giving me circular dependency concurrent issue