Forum Discussion

marcmen's avatar
marcmen
Helper I
4 years ago
Solved

Amortization Balance

Hi All, I am looking for some help to create either a new column or a measure that will show a running balance based on the amortization script below.    Amortization HW = SUMMARIZE(filter(CROSSJO...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi marcmen ,

     

    You could create a measure to get the cumulative value for [HW CapEx].

    cumulativ_ = CALCULATE(SUM(AOPF[HW CapEx]),FILTER(ALLSELECTED(AOPF),AOPF[Date]<=MAX(AOPF[Date])))

     Then use the starting balance minus [cumulativ_].

     

    Best Regards,

    Jay

marcmen's avatar
marcmen
Helper I
4 years ago

amitchandakThanks for the quick replay

Unfortunately, my company prevents the sharing of data so I will try and explain this the best I can.

For a little background - this matrix will be used to export its values into an Excel spreadsheet (below) for accounting. The required amortization values for the export will be capped at 2 years from the start date to the end of that fiscal year with the remaining balance being presented as a lump sum. For example - a 48-month amortization schedule starting in June would show 18 months of payments and the remainder would be forwarded. My objective is to be able to create a "Difference" column like the one below

I currently have a matrix that matches the above without the end of the fiscal summary

 

I have three tables that are being used to create this matrix

  1. A project table that contains the original purchase Amount (AOPF[xxxx List Price]

     

  2. Several Amortization Tables as depicted earlier (XXX Amortization)
  3. A date table (Date)

     

Please let me know if you require any more information.

Thank you