# Thread: Product of Previous Value Thanks: 0 Likes: 0

1. ## Product of Previous Value

Good morning, I am working in PowerPivot and need a column that take the value from the previous row and multiplies it by the value of another column in the same row...

 Multiplier Value Final Value 2% 100 102 3% 102*3% 4% 102*3%*4% 5% 102*3%*4%*5%

I am lost about how to calculate the "Final Value" column. Any help would be greatly appreciated.

Thank you,
Chase

P.S. My company only has PowerPivot installed, so I have to accomplish this exclusively in PowerPivot without using the other Power BI tools.

2. ## Re: Product of Previous Value

I don't know the first thing about Power Pivot (Excel 2010 here) but maybe this will help anyway.

In C3 (of your example) =C2*A3 then drag down.

3. ## Re: Product of Previous Value

63Falcondude, thank you for your reply. PowerPivot doesn't use cell references which is why I am having trouble figuring out how to accomplish this.

EDIT: My original post excluded some of the complications. I was afraid I might be given a good solution and not know how to add the filters, so the edit below includes my full problem...

Good morning, I am working in PowerPivot and need a column that takes the value from the previous row and multiplies it by the value of another column in the same row. It gets a little more complicated than that; the default value is 100 until "Age" = 4, at "Age" = 4 you take the "Value" within the same row multiplied by the "Multiplier", after "Age" = 4 you take the previous value and multiply by the "Multiplier". Example below:

 Age Multiplier Value Final Value 1 1% 50 100 2 1% 50 100 3 2% 50 100 4 2% 100 102 5 3% 50 102*3% 6 4% 50 102*3%*4% 7 5% 50 102*3%*4%*5%

I am lost about how to calculate the "Final Value" column. Any help would be greatly appreciated.

Thank you,
Chase

P.S. My company only has PowerPivot installed, so I have to accomplish this exclusively in PowerPivot without using the other Power BI tools.

4. ## Re: Product of Previous Value

Turns out 2016 has a ProductX function, but I am working in 2013. I found an external thread that suggests using EXP() and LN()...

I created a pre-calculation column that has the values I want the products so my data currently looks like this:

 Age Multiplier Value Pre-Calculation 1 1% 50 2 1% 50 3 2% 50 4 2% 100 102% 5 3% 50 3% 6 4% 50 4% 7 5% 50 5%

The pre-calculation field is working how I would like for it to, but I am getting a "calculation" error in the final calculation. My final calculation looks like this:
=EXP(
SUMX(
FILTER(Table,
Table[Age]>=4
&& Table[Age]<=Earlier(Table[Age])
),
LN(1+Table[Pre-Calculation])
)
)

I am desperate for help on this. Thank you to anyone that can help me sort this out.

6. ## Re: Product of Previous Value

Well... that suggestion is similar to the one I found, but they setup the formula slightly different. I tried this way and I am still getting this same error:

Calculation error in column 'Table'[]: An argument of function 'LN' has the wrong data type or the result is too large or too small.

This is my new equation based on the link:

=CALCULATE(
SUMX(Table, LN(1 + Table[Pre-Calculation])),
FILTER(Table,
Table[Age]>=4
&& Table[Age]<=Earlier(Table[Age])
)

7. ## Re: Product of Previous Value

PS, I have tried taking out the "1+" form the LN() part of the calculation and still get the same error

8. ## Re: Product of Previous Value

Can you leave out the LN and just have the multiplier and this:

Cumulative Total – DAX Patterns

?

9. ## Re: Product of Previous Value

Hi,

I have a similar problem actually, I wanna do a recursive calculation in powerpivot but the calculation little bit different.
Well actually I have posted my case in another thread.
The data is like this:

 R\C A B C D Formula for D Column 0 0 0 B - A = 0 0 0 1 100 200 B - A = 100 100 =IF(D0+C1<0,0,D0+C1) 2 70 0 B - A = -70 30 =IF(D1+C2<0,0,D1+C2) 3 100 0 B - A = -100 0 =IF(D2+C3<0,0,D2+C3) 4 100 300 B - A = 200 200 =IF(D3+C4<0,0,D3+C4)

Can you help me solve my case?

Thanks,
Budy from Indonesia

10. ## Re: Product of Previous Value

Hey there, I was able to solve my issue using the link sheetspread provided. I was getting some errors before, and honestly I have no idea what I changed, but it is working now.

Pociners - Your issue is a little different because you can use calculate(sum()) with some filters to get your answer. I actually had the same issue and it was solved in this post http://www.mrexcel.com/forum/power-b...ame-table.html the same solution should work for you, just modify your adjusters so that the R/C numbers is less than the EARLIER(R/C)