Excel 2013 PowerPivot Calculated Column question

[john234]

I am trying to create a calculated column in a single table Power Pivot file using a conditional IF expression.

My first column only contains random recurring values of 5,9 and 0. My goal for the new calculated column is to replace the instances of 0 with the last prior row's instance of either a 5 or a 9 (see visual below).

Column1data New Calculated Column2Desired Result

9 9
0 9
0 9
5 5
9 9
0 9
0 9
5 5
0 5
0 5
5 5


Any suggestions on a calculated column formula on how to accomplish this seemingly simple task?

I assume I may have to use an index column, or look up function? My first draft of the calculated column is:

=if([Column1data]=5,5,IF([Column1data]=9,9,?previous non zero value in [Column1data]?))

 

[scottsen]

You will certainly need another column to properly "order" the rows.