Excluding data from calculation if it's not between two values (the previous and next row)

[Anonymous]

I'm trying to calculate average time remaining to the end of the work order for all steps and work orders in our database, specific to the part number. Essentially we're trying to see how long on average each step should take before work order completion (for each part number). We break down the work orders into steps and each step is supposed to go in sequence. We just discovered that sometimes Step 1 can be done in the middle of the work order, regardless of step order. I'd like to exclude these values from the overall average calculation.

This is what a "bad" work order looks like. Step 1 falls between step 20 and 21. I would like to exclude this value from the final calculation.

My thought process on how to do this is simple, but hard to put into DAX. I need something along the lines of: if the current line (step 1) is less than the previous line (step 20) and less than the next line (step 21) for a work order, exclude the current line (step 1) from the calculation.

I attempted to create an exact copy of the table in Power Query, then tried to use lookupvalue to obtain the previous and next step numbers on the same line as the current step. This would allow me to do the following check: If(Current Line<Previous Line && Current Line < Next Line, calculate the function, blank()). I just can't figure out how to pull the previous and next line into the current line to do this check before calculating averages.

This is a sample of my data which contains two work orders, one that's normal (steps in order) and one that we're trying to fix (steps not in order). I cannot post the Power BI file itself because it contains confidential information, but I've scrubbed these two example work orders. Any help would be greatly appreciated.

[v-tangjie-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a calculated column and a table.

Column = 
var _a =CALCULATE(MAX('Table'[Step Rank]),FILTER('Table','Table'[WorkOrder Rank]=EARLIER('Table'[WorkOrder Rank])+1 && 'Table'[WorkOrder]=EARLIER('Table'[WorkOrder])))
var _b =CALCULATE(MAX('Table'[Step Rank]),FILTER('Table','Table'[WorkOrder Rank]=EARLIER('Table'[WorkOrder Rank])-1 && 'Table'[WorkOrder]=EARLIER('Table'[WorkOrder])))
return IF('Table'[Step Rank]<_a && 'Table'[Step Rank]<_b,1,0)

Table 2 = FILTER('Table','Table'[Column]=0)

(3) Then the result is as follows and you can then perform the calculations in Table 2.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Anonymous]

My table did not upload when giving an example of a "bad" work order. Here's how the Step Rank looks and what we're trying to exclude: