Help needed on expression

[Jonesy]

Hi, Can someone help me please. I am creating an app for recording farm work. I am trying to capture a date ([First Permissible Harvest Date]) from a previous record in the same table, however, I am looking to get the highest first permissible harvest date, from rows containing the same "Batch Number" as the [Batch Number] in this row. Hope that makes sense.

I have tried a good few different expressions, LOOKUP etc but the closest I have got to this is the following expression:

IF([Operation] = "Harvesting", (MAX(Table[First Permissible Harvest Date])),'')

The problem I have is I am getting the Highest [First Permissible Harvest Date] from all rows, Not just rows which share the same [Batch Number] as the current row being created.

Any help greatly appreciated.

James

[TeeSee1 · Accepted solution]

You can try

IF(
 [Operation] = "Harvesting", 
 MAX(
  SELECT(Table[First Permissible Harvest Date], [Batch Number]=[_THISROW].[Batch Number])
 ),
 ""
)

[Jonesy]

Thank you TeeSee1, that is exactly the solution I needed!! Perfect! Thanks again.

James