Sumproduct equivalent

reggieneo
Participant V

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.

0 11 307
11 REPLIES 11

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.

reggieneo
Participant V

hi Marc,
please see, timestamp included:

Hmm, ok. Where are you using the expression?

reggieneo
Participant V

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

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?

reggieneo
Participant V

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.

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?

reggieneo
Participant V

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

reggieneo
Participant V

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"

)

Is your issue solved?

yes Marc. Thanks

Top Labels in this Space