topic Value previous row per Order in DAX Commands and Tips

Value previous row per Order

joshua1990 — Sun, 16 Apr 2023 12:34:23 GMT

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?

Re: Value previous row per Order

FreemanZ — Sun, 16 Apr 2023 14:18:35 GMT

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:

Re: Value previous row per Order

joshua1990 — Sun, 16 Apr 2023 16:58:54 GMT

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?

Re: Value previous row per Order

ThxAlot — Mon, 17 Apr 2023 01:08:26 GMT

Super easy, nothing but one click on the button.

DAX measure is also working well.

At least 2 solutions in calculated column

Re: Value previous row per Order

FreemanZ — Mon, 17 Apr 2023 05:44:21 GMT

hi joshua1990

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

Is there something i didn't get?