Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
4 years ago
Solved

Calculated Column - Last Entry per Sales Order

Hi experts!

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

Order NumberStatusValue
100505
100665
100804
10095 
101505

 

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?

Need Help
  • Dhacd's avatar
    Dhacd
    4 years ago

    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.

3 Replies

  • Dhacd's avatar
    Dhacd
    Resolver III
    4 years ago

    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.

      • Dhacd's avatar
        Dhacd
        Resolver III
        4 years ago

        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.