Value previous row per Order

[joshua1990]

Hi community!

I have a table with values per orders for each step. 

Order	Step	Value
101	1	5
101	2
101	3	4
102	1	6

As you can see not every row has a value.

Now if a value is missing, then I would like to add the last available qty for each order based in the column Step.

How would you do this?

[ThxAlot · Accepted solution]

Super easy, nothing but one click on the button.

DAX measure is also working well.

At least 2 solutions in calculated column

[ThxAlot · Accepted solution]

Super easy, nothing but one click on the button.

DAX measure is also working well.

At least 2 solutions in calculated column

[FreemanZ]

hi @joshua1990 

try to add a calculated column like:

Column2 = 
VAR _table =
    FILTER(
        Data,
        Data[Order]=EARLIER(Data[Order])
            &&Data[Step]<=EARLIER(Data[Step])
            &&Data[Value]<>BLANK()
    )
VAR _steppre =
    MAXX(_table, Data[Step])
VAR result =
    MAXX(
        FILTER(
            _table,
            Data[Step]=_steppre
        ),
        Data[Value]
    )
RETURN
    result

or 

Column = 
VAR _table =
    FILTER(
        Data,
        Data[Order]=EARLIER(Data[Order])
            &&Data[Step]<EARLIER(Data[Step])
    )
VAR _steppre = 
    MAXX(_table, Data[Step])
VAR _valuepre =
    MAXX(
        FILTER(
            _table,
            Data[Step]=_steppre
        ),
        Data[Value]
    )
VAR result =
    IF(
        ISBLANK([Value]), 
        _valuepre, 
        [Value]
    )
RETURN 
    result

it worked like:

[joshua1990]

@FreemanZ : Thanks a lot! That works perectly well.

But I missed 1 key information - I'm sorry for that.

What if two rows are blank? Based on the approach above just the first blank will be replaced.

Any idea here as well?

[FreemanZ · Accepted solution]

hi @joshua1990 

the code shall still work. i tried with such data below:

Is there something i didn't get?