Forum Discussion

marcmen's avatar
marcmen
Helper I
4 years ago
Solved

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:

  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's avatar
    Anonymous
    4 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)+1
    return
    'Table'[value]/_years

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

     

    Best Regards,

    Jay

2 Replies

  • Anonymous's avatar
    Anonymous
    Not 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)+1
    return
    'Table'[value]/_years

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

     

    Best Regards,

    Jay

    • marcmen's avatar
      marcmen
      Helper 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.