Forum Discussion
Amortization Help
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:
- 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.
- 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
- Anonymous4 years ago
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)+1return'Table'[value]/_yearsIf I misunderstand your meaning, please share some sample data and expected result.
Best Regards,
Jay
2 Replies
- AnonymousNot applicable
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)+1return'Table'[value]/_yearsIf I misunderstand your meaning, please share some sample data and expected result.
Best Regards,
Jay
- marcmenHelper I
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.