Forum Discussion
Post ProdigyValue previous row per Order
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?
Super easy, nothing but one click on the button.
DAX measure is also working well.
At least 2 solutions in calculated column
hi joshua1990
the code shall still work. i tried with such data below:
Is there something i didn't get?
4 Replies
- ThxAlot
Super User
Super easy, nothing but one click on the button.
DAX measure is also working well.
At least 2 solutions in calculated column
FreemanZhi 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 resultit worked like:
joshua1990FreemanZ : 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
hi joshua1990
the code shall still work. i tried with such data below:
Is there something i didn't get?