Need help creating a calculated column to return the previous row's text value

[TL_675789]

Hi,

I have a data set which contains orders and all of the statuses within an order are ranked. I have an existing calculated column that has the status name with a # appended to the end to represent the instance of that specific status. I would like to create a new column that simply pulls the previous status from this column. Below is a sample of how the data is structured and the last column is the one I am trying to create. 

Order #	Sequence	Task Status	Calculated Column
54678	1	Approved1	null
54678	2	Repair1	Approved1
54678	3	QC1	Repair1
54678	4	Approved2	QC1
78901	1	Repair1	null
78901	2	Audit1	Repair1
78901	3	Delivered1	Audit1

[amitchandak]

User earlier

https://community.powerbi.com/t5/Desktop/Explanation-of-the-EARLIER-formula/td-p/529469

https://community.powerbi.com/t5/Desktop/FILTER-EARLIER-expression-just-how-is-it-working/td-p/24682...

https://docs.microsoft.com/en-us/dax/earlier-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[TL_675789]

@amitchandak  I have tried a few versions of the earlier function and just can't get the values that I am expecting. I tried the DAX code below and it just returns the same value as the current row.

Calculated Column = 
VAR CurrentRow = Table[sequence]
VAR CurrentStatus = Table[status]
VAR PreviousDate =
    CALCULATE (
        MAX ( Table[sequence] ),
        FILTER ( ALL ( Table ), Table[sequence] < CurrentRow && Table[status] = CurrentStatus)
    )
RETURN
    CALCULATE (
        SELECTEDVALUE( Table[status] ),
        FILTER ( ALL ( Retail_Inventory_Revenue_Tasks ), Table[sequence] = PreviousDate && Table[status] = CurrentStatus)
    )

[TL_675789]

I figured it would be some instance of the earlier function, but I just can't get it to work. Any suggestion on how I would use it in this specific example? 

I tried the code below, but it just returns the same value. 

calculated column = calculate(min([Task Status]),[Sequence]=earlier[Sequence])

[v-diye-msft · Accepted solution]

Hi @TL_675789 

I'd like to suggest you using measure rather than calculated column:

Measure 5 = var a = MAX(Sheet[Sequence])-1
var b = CALCULATE(MAX(Sheet[Task Status]),FILTER(ALL(Sheet),[Sequence]=a),VALUES(Sheet[Order #]))
Return b