Queue Time Calculation in PowerPivot

[jmhutchi]

Hi all: First time poster, long time lurker

I am using powerpivot to analyze production lot history. My columns are these:

Lot Number, Split, Major Step, Substep, Operator, DateTimeIn, DateTimeOut

The dataset has multiple lot numbers and split processed simultaneously, so analysis needs to be filtered for those contexts.

I can easily calculate the process time for each substep:
SubStepProcessTime := 1.0*(DateTimeOut-DateTimeIn)

I want to calculate the queue time for each substep, which is:
SubStepQueueTime := 1.0*(DateTimeIn (Current Row) - DateTimeOut (previous step))

I'm stumped.

One idea I had that I dont know how to implement: Is there some way to find the next most recent DateTimeOut less than the DateTimeIn(Current Row)?

Any ideas?

 

[ImkeF]

Welcome jmhutchi,
I'd go for a calculated column:
=[DateTimeIn]-CALCULATE(MAX([DateTimeOut]); FILTER(Table; [DateTimeIn]<EARLIER([DateTimeIn])))

This only works if your the previous step can be identified as the one that has the "earliest" start date before you current step (your production runs in sequence). Otherwise you should consider creating an index that clearly identifies the relevant order.

hth, Imke