Fill blank values with previous value in the same column (linear increas)

Hi Anonymous ,

These type of questions are the most fun because it allows me to really be creative 😄 Anyway, here is a solution for you. 

First I have created a ranking column based on date. The reason is that I don't know if your real dataset has patches of empty values or only a few empty rows at the end of the dataset. Either way, I need to fill it up based on the last known value with a growth factor of 1.02. Thus, a ranking will come in handy later:

Ranking = RANKX(Table1, Table1[Date], , ASC, Dense)

Next, I created a calculated column with the following DAX:

Filled = IF(Table1[Value] = BLANK(),
    VAR _curRank = Table1[Ranking]
    VAR _lastNonBlankRank = MAXX(FILTER(table1, Table1[Ranking] < _curRank && Table1[Value] <> BLANK()), Table1[Ranking])
    VAR _lastNonBlankValue = LOOKUPVALUE('Table1'[Value], Table1[Ranking], _lastNonBlankRank)
    RETURN
    _lastNonBlankValue * POWER(1.02, _curRank - _lastNonBlankRank), 
    Table1[Value])

Basically it returns the value in the [Value] column if there is one, but if there isn't we are going to do something special. First we store some variables, like current row ranking number, last ranking number that has a non-blank value in the [Value] column and the actual last non-blank value of the [Value] column. Then we return the last non-blank value * 1.02 ^ (thisrow ranking - last non blank ranking). This returns the following: (I changed the data layout to a maximum of 8 decimals so this would be easily compared to your example)

Let me know if this is clear and meets your requirements 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Hi Anonymous 
Yes we absolutely can! I created this measure (it will only work in a table visual with all the rows of the original table):

FilledAsMeasure = 
IF(SELECTEDVALUE(Table1[Value]) = BLANK(),
    VAR _curRank = SELECTEDVALUE(Table1[Ranking])
    VAR _lastNonBlankRank = MAXX(FILTER(ALL(table1), Table1[Ranking] < _curRank && Table1[Value] <> BLANK()), Table1[Ranking])
    VAR _lastNonBlankValue = LOOKUPVALUE('Table1'[Value], Table1[Ranking], _lastNonBlankRank)
    RETURN
    _lastNonBlankValue * POWER(1+Growth[Growth Value], _curRank - _lastNonBlankRank), 
    SELECTEDVALUE(Table1[Value]))

I created a parameter called Growth with min=0, max = 0.2 and step =0.01. 

You can find my PBIX here (Table1 and page AndresSantest, ignore other tables please).: https://1drv.ms/u/s!Ancq8HFZYL_aiJByiLcdu4Aniq5kvA?e=MD7x8J 

Let me know if this solves your issue and please mark it as the solution if it does 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂