Calculate a compound formula

[MarkFay1]

I am trying to create a calculation where I want to know the the average volume over the term of a contract given a certain percentage increase per year. I have the term of the contract, the originating volume and projected increase for the year. So I am thinking the math is pretty straight forward, (original volume * increase)+original volume=new volume, but that is good for year one. Year two will be (new volume*increase)+new volume=newer volume. That will need to be repeated as many times as the term in years of contract. 

My plan was to create a collection(StudyCount) using a "while/do until loop" I found (A while / do-until loop in PowerApps? - Dynamic People). This actually works well if just doing simple math or just adding a field. Within the loop I thought I could just create a variable for the volume and just use that.
Something Like

If(varRuns=0,UpdateContext({YearCount:OrigionalVolume}));

Collect(StudyCount,{Volume:(YearCount*increase)+YearCount});

UpdateContext({YearCount:Last(StudyCount.Volume)})

And then the loop would update varRun and repeat the process.

Once ran the collection should give me a row for each year then I can add the volumes in the collection and divide by the term and get my average.

I have not been able to test my theory because I get a Invalid argument type in the  collection line underlining the YearCount. Will this formula work? Can I not do a calculation off of a variable?

If not what would be a viable alternative?

Thanks

[WarrenBelz]

Hi @MarkFay1 ,

A bit of fried brain on this one, but the code below is valid using some test values and assuming the increase is a percentage

ClearCollect(
   StudyCount,
   ForAll(
      Sequence(NoOfYears),
      {
         Volume: 
         (OriginalVolume * (Value - 1)) + (increase * 
         Power(
            1 + (increase / 100),
            Value
         ))
      }
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[MarkFay1]

Thanks WarrenBelz, yea this was a bit tricky to explain.

I tried what you suggested but it didnt like the Sequence function or the YearCount

Mark

[WarrenBelz]

Hi @MarkFay1 ,

I got that from the code you posted - I assumed it was the number of years. You need to specify the number of years it will run for. 

ClearCollect(
   StudyCount,
   ForAll(
      Sequence(YearNosRequired),
      {
         Volume: 
         (TextInput3 * (TextInput7 - 1)) + (wInc * 
         Power(
            1 + (TextInput7 / 100),
            Value
         ))
      }
   )
)

The code is valid - see below - 120 original, 8% for 5 years

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[MarkFay1]

Thank you! I saw my mistake and it is now functioning. However the math is not adding up. I contribute this to my not explaining my objective well. What I am trying to figure out is the average volume over the term. As an example, given the chart below, I want to take the originating volume and multiply the percent increase to determine at the end of that year what the volume would be. Then use that year end volume to start the same calculation for year two and so on. So in the example below the end of the 6th year there would be a volume of 716432. I then need to get an average of volume for those 6 years (which is simple enough once I get a collection of the yearly volumes)

Initial year volume	Year Increase	Ending year volume
600000	0.03	618000
618000	0.03	636540
636540	0.03	655636
655636.2	0.03	675305
675305.3	0.03	695564
695564.4	0.03	716431

How do I pick up the year end volume to use for the next years calculation?

PS, thanks for the tip on using Sequence, much easier than what I was doing.

Mark

[WarrenBelz]

@MarkFay1 ,

That is the year end volume (which is also the start of the next year) in the calculation. If you want the Average

UpdateContext(
   {
      varAverage: 
      Average(
         colYears,
         Amount
      )
   }
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[MarkFay1]

But given your example;

Shouldn't the numbers come out like this:

Origional	%	year End
120	0.08	130
130	0.08	140
140	0.08	151
151	0.08	163
163	0.08	176

In your sample shouldn't the year end amount for year 2 be 140 (129*8%+129)? how does it jump to 259?

Thanks

Mark

[WarrenBelz · Accepted solution]

@MarkFay1 ,

My understanding was that you want another volume added each year - just get rid of the first bit

ClearCollect(
   StudyCount,
   ForAll(
      Sequence(YearNosRequired),
      {
         Volume: 
         TextInput3 * 
         Power(
            1 + (TextInput7 / 100),
            Value
         )
      }
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[MarkFay1]

Sorry for not explaining well..

Yes, that was it, that works!

Thank you!