Calculated Column Referencing Next Row

[newgirl]

Hi!

So I have this set of data with each row representing the trip of the truck from Lining up in the Plant (Inline_Plant) until the time they return to the plant (Return_Plant).

Trip Reference	Plate Number	REASON CODE	STATUS	INLINE_PLANT Date	INLINE_PLANT Time	RETURN_PLANT Date	RETURN_PLANT Time
91	AAA	<CH>	Completed	1-Jan	8:00 AM	2-Jan	8:00 AM
51	AAA	<CH>	Completed	2-Jan	8:01 AM	4-Jan	1:00 PM
59	AAA	<H>	Cancelled	4-Jan	1:00 PM
92	AAA	<H>	Completed	6-Jan	7:00 AM	6-Jan	12:00 PM
81	BBB	<LH>	Completed	1-Jan	8:00 AM	2-Jan	12:30 AM
70	BBB	<E>	Cancelled	2-Jan	12:31 AM
69	BBB	<LH>	Completed	3-Jan	6:00 AM	5-Jan	10:00 AM
85	BBB	<E>	Assigned	5-Jan	10:05 AM	5-Jan	10:30 AM

I need to add a calculated column in the data to know how many hours to add to the trip based on the following conditions:

If Trip Status is	and If Reason Code of Trip is…	Check the next trip?	If next Trip's Status is…	Add how many hours to Trip
Not equal to Completed	n/a	no	n/a	0
Completed	0 or blank	no	n/a	0
Completed	<H>	no	n/a	0
Completed	<X> One letter not equal to H	no	n/a	0
Completed	<XX> Two letters with H in 2nd	yes*	Cancelled	0
			Not equal to Cancelled	1
*Next trip should be same plate number. If different plate number, add 0 hours to trip

As you can see, the conditions would need to check the value in the next trip and I don't know how to reference values in the next row in Power BI.

Here is the desired output.

Trip Reference	Plate Number	REASON CODE	STATUS	INLINE_PLANT Date	INLINE_PLANT Time	RETURN_PLANT Date	RETURN_PLANT Time	Desired Output
91	AAA	<CH>	Completed	1-Jan	8:00 AM	2-Jan	8:00 AM	1
51	AAA	<CH>	Completed	2-Jan	8:01 AM	4-Jan	1:00 PM	0
59	AAA	<H>	Cancelled	4-Jan	1:00 PM			0
92	AAA	<H>	Completed	6-Jan	7:00 AM	6-Jan	12:00 PM	0
81	BBB	<LH>	Completed	1-Jan	8:00 AM	2-Jan	12:30 AM	0
70	BBB	<E>	Cancelled	2-Jan	12:31 AM			0
69	BBB	<LH>	Completed	3-Jan	6:00 AM	5-Jan	10:00 AM	1
85	BBB	<E>	Assigned	5-Jan	10:05 AM	5-Jan	10:30 AM	0

Can somebody help me on the formula for the calculated column?

[v-kelly-msft · Accepted solution]

Hi @newgirl ，

First create an index column;

Then create a measure as below:

Measure = 
 IF(
    MAX('Table'[STATUS])<>"completed",
      0,
    IF(MAX('Table'[STATUS])="completed",
    IF(SEARCH("H",MAX('Table'[REASON CODE]),1,0)<>3,0,
    var _nexttrip=CALCULATE(MAX('Table'[STATUS]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])+1&&'Table'[Plate Number]=MAX('Table'[Plate Number])))
   Return
    IF(_nexttrip="Cancelled",0,1))))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@newgirl See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...