Hi, so I have this expression:
sum(select(Materials[Quantity], and([Material] = [_THISROW].[Material], [Batch]=any(Open_Batches[Batch ID])))) - any(select(Leftovers[Quantity], and([Material] = [_THISROW].[Material], [Batch] = any(Open_Batches[Batch ID]).[PreviousBatch])))
It runs from a behaviour iterating through each material type, and calculates the total used for each material type for the batch by summing the quantities of materials in another table (these rows belong to the batch), and subtracting the amount the batch started with (=the leftovers from the previous batch, also rows in another table belonging to the batch). It mostly works fine, the only problem is that the second statement is returning leftovers belonging to the current batch rather than the previous one, which is what I want.
Open_Batches is a slice. It only ever contains one row.
[PreviousBatch] is a ref to another batch in Batches.
Leftovers.[Batch] is also a reference to a batch (the one it belongs to).
I really can’t see why this isn’t behaving as expected, so any help would be appreciated.