Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
User earlier
https://community.powerbi.com/t5/Desktop/Explanation-of-the-EARLIER-formula/td-p/529469
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
@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)
)
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.
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
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
98 | |
89 | |
74 | |
67 | |
62 |