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(CROSSJOIN(AOPF,'Date'),

'Date'[Date]>=(AOPF[Project Start Date]) && 'Date'[Date]<=(AOPF[Hardware Amortization End Date])),

[Year-Month],[Date],[Project Title],

"HW CapEx", Max(AOPF[Hardware CapEx])/(DATEDIFF(Min(AOPF[Project Start Date]),Max(AOPF[Hardware Amortization End Date]),DAY)+1),

"HW OpEx", MAX(AOPF[Hardware OpEx])/(DATEDIFF(Min(AOPF[Project Start Date]),Max(AOPF[Hardware Amortization End Date]),DAY)+1))

 

 

 

My desired result would be something like this:

2023/01/02 (AOPF[Hardware CapEx]) - $2 = $4998.00.

2023/01/03 (AOPF[Hardware CapEx]) - $2 = $4996.00.....

Assuming that (AOPF[Hardware CapEx]) starting balance was $5000.

Thanks

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      Anonymous  - that worked  -  thank you

    • 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