Sumproduct equivalent

[anonymous]

Hi All,
I am using this expression to get the most recent value based on some criteria. i always ended up to the last value in column:
ANY(
SELECT(
Order Details[Dipstick Closing],
(
[timestamp]
= MAX(
SELECT(
Order Details[timestamp],
AND( ([timestamp] < [_THISROW].[timestamp]),[Product Name]=[_THISROW].[Product Name])
)
)
)
)
)

please help again thanks.

[Marc_Dillon]

Your expression appears correct. I can’t see any issues, but given that you’ve not included the timestamp column in your screenshot, that makes it my prime suspect.

[anonymous]

hi Marc,
please see, timestamp included:

[Marc_Dillon]

Hmm, ok. Where are you using the expression?

[anonymous]

as an initial value of a child table. hope i answered you correctly.

[Marc_Dillon]

So, you’re NOT using it in the context of this highlighted row? What is the value of the timestamp in whatever record you ARE using it from?

[anonymous]

oh your right. I set the timestamp column to be hidden and I didn’t able to see that I was referring to the wrong timestamp “[_thisrow ].[Timestamp]”.

now that is my challenge. how can i get that [dipstick closing] value? i am using it on edit mode as sometimes user forgot to input the whole sets of product data.

i cant wrap around in my head in building this expression. I need to refer to the dipstick id as they are unique to this set of transaction/input.

[Marc_Dillon]

What do you mean wrong timestamp? Which record/context ARE you trying to run the expression from? Are you trying to get the 2nd to most recent value?

[anonymous]

please see image:

when user forgot to input a product on child’s table, user must go into edit mode to add the missing product. but the new data must match the parent [dipstick ID].
the expression should be able to locate the most recent value of the row being edited.

sorry if i consume a lot of your time on this one

[anonymous]

HI All
thanks @June_Corpuz for assisting me here: this is another expensive formula:

LOOKUP(
MAX(
SELECT(
Order Details[_rownumber],
AND(
[Product Name] = [_thisrow].[Product Name],
[timestamp]

			<

			LOOKUP(
				MAX(
					SELECT(
						Order Details[_ROWNUMBER],
						[Dipstick  ID] = [_THISROW].[Dipstick  ID]
					)
				),
				"Order Details",
				"_ROWNUMBER",
				"timestamp"
			)	
		)
	)
),
"Order Details",
"_ROWNUMBER",
"Dipstick Closing"

)

[Marc_Dillon]

Is your issue solved?

[anonymous]

yes Marc. Thanks