Calculated Column - Last Entry per Sales Order

[joshua1990]

Hi experts!

I have a table that shows me the value for each sales order for the respective state:

Order Number	Status	Value
100	50	5
100	66	5
100	80	4
100	95
101	50	5

As you can there are missing entries in the column Value.

I would like to create a calculated column that shows me per sales order for blank entries the last entry based on column status.

But how?

[Dhacd]

Hi @joshua1990,
Just to check whether we are on the same page, 
In this context you want the blank row to have the value of previous number status but same order id
That is 4.
If this is what you are trying to achieve it is possible.
Reply whether I understood your problem correctly or not.
Regards,

Atma.

[joshua1990]

@Dhacd : Exactly!

[Dhacd · Accepted solution]

Hi @joshua1990 

Try the below code

NullValueFill = 
Var _Status = 'Table'[Status]
Var _Value ='Table'[Value]
Var _PreviousStatus = maxx(filter('Table','Table'[Status]<_Status),'Table'[Status])
Var _PreviousValue = CALCULATE(Max('Table'[Value]),filter('Table','Table'[Status]=_PreviousStatus))
Var result = If(isblank(_Value),_PreviousValue,_Value)
Return result

If there is any issue please reply.

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.
Regards,
Atma.