[Installation Workorder] = // calculated column
if( T[Work order type] = "inspection",
// Find the most recent workorder
// for the same site where order type
// is equal to "installation". This will
// work OK only on condition that the
// workorders can be unambiguously ordered
// on the time axis.
// Bear in mind that T[Work order date]
// must be of the datetime/date type, not
// a string.
var __currentOrderDate = T[Work order date]
var __currentSite = T[Installation site]
var __potentialWorkorders =
topn(1,
filter(
T,
T[Installation site] = __currentSite
&&
T[Work order type] = "installation"
&&
T[Work order date] < __currentOrderDate
),
T[Work order date],
DESC
)
var __error =
// Either there must be one order for the
// above conditions or no such order. If
// there is more than 1 such order, it's
// an error condition. It might also be
// that if there's no such order it's an
// error condition. Then you have to change
// this logical condition to suit your needs.
// Bear in mind that you can also raise an
// error with the ERROR() function if you
// wish.
COUNTROWS( __potentialWorkorders ) > 1
var __workorder =
if( not __error,
MAXX(
__potentialWorkorders,
T[Work order ID]
),
"not possible to determine"
)
return
__workorder
)
