itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: fill blank cells with previous values

This article talks about how to put the previous value for blank cells like that:

power bi power bi

The most important is to have a column with values that I can use to sort my data correctly so it can be an index column or a date column for instance. To create an index column, read Power BI: sort correctly with index/ranking.

Option 1: calculated column

For a column, click on:

power bi

Then put this formula:


var check=CALCULATE(MAX('table'[argument1]),FILTER(ALL('table'),'table'[argument1]<=EARLIER('table'[argument1]) && 'table'[argument2]<>BLANK()))
RETURN
CALCULATE(MAX([argument2]),'table'[argument1]=check,ALL('table'))
power bi

NOTE: to do for a category, add: && 'table'[argument3]=EARLIER('table'[argument3])

power bi

Option 2: measure

For a measure, click on:

power bi

Then put this formula:


var check=CALCULATE(MAX('table'[argument1]),FILTER(ALL('table'),'table'[argument1]<=MAX('table'[argument1]) && 'table'[argument2]<>BLANK()))
var result=CALCULATE(MAX([argument2]),'table'[argument1]=check,ALL('table'))
RETURN
IF(ISBLANK(result),"",result)
power bi

NOTE:

  • To do for a category, same as the calculated column but change EARLIER by MAX
  • In my example, the row 1 is blank until row 3 so to exclude them, after “RETURN”, put only “result”
power bi

Interesting Topics