Amortization Help

[marcmen]

Hi all, I am hoping that someone can help me out. I am a bit of a noob when it comes to Power BI but because I have touched it once or twice, I was voluntold for this project.

I am creating a financially focused project planning dashboard intended to plan for next year’s initiatives. The area I am stuck on is year-over-year rollups of amortized payments.  The objective is to capture what the cost will be within a fiscal year. For example, if a project starts in June of this year, costs $1000, and has a 1-year amortization schedule then my cards should read $500 for this year and $500 for next. Below is a screenshot of the fields that the department heads will use to input their data.

What I need assistance with at this point is:

1. I have a project start date earlier in the form. I was trying to use DATEADD to dynamically determine the end date of the amortization schedule, but I cannot get it to accept the value from the field entry.
2. I assume that a combination of this blog How to divide/distribute values between start date... - Microsoft Power BI Community and slicers will address the remainder of my issues but if you have any suggestions I am open to them

[Anonymous · Accepted solution]

Hi @marcmen ,

Assum you have a column for the days that a project might need.

Then you could directly add the days with the start date.

Then use datediff function to get the years between start date and end date.

Column 2 =

var _years = DATEDIFF('Table'[start date],'Table'[Column],YEAR)+1

return

'Table'[value]/_years

If I misunderstand your meaning, please share some sample data and expected result.

Best Regards,

Jay

[marcmen]

@Anonymous  - sorry for the delay with my response. I opted to create a calculated column in the source SharePoint list so I have not tried your solution. all I was looking to do was Date + Number from column  = new date. 

I will try your solution as soon as I have a few minutes.