Finding a value based on a comparison of two collections

(0) Share

Report

Posted on by Project2021

Hello,

I have two collections, which I want to compare to get a certain value based on a variable.

Let me sketch te situation. I have a collection like this, where there a different models that determine the worth of something based on the months the product is old.

Collection name: "Value"

Name:	Month1:	Month2:	Month3	Month4:	Month5:
ModelX	75%	70%	65%	60%	55%
ModelY	70%	60%	50%	40%	30%

The second collection has a row with name, a selected model from the collection above., a starting price and the age in months. Based on that I want to calculate te current prize.

Collection name: "Products"

Name:	Model:	starting Price	Age (in months)	Current price
ProductX	ModelX	€100,-	2	?
ProductY	ModelY	€50,-	3	?

For example ProductX was €100 at the start but because it's 2 months old it should only have 70% left off the starting price. So €70,-. ProductY should take modelY to get the current price to 50%, which would be €25,- left.

So basically I'm looking for a way to get the right percentage based on the selected model and age in months from another collection. And then use it to calculate the current prize based on the start price and the right percentage.

Can someone help me figure this out?

Thanks in advance!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (2)

Project2021 40 on at

Like (0)

Report

I'm very close to the solution I think. I have made
LookUp(Value; Name = ThisItem.Name;Month1)
Now I have the value for the first month in my gallery.

I only need to make Month1 in the code based on the "age" of the collection "Products".

Was this reply helpful? Yes No
Verified answer

rizkyaditya 292 on at

Like (1)

Report
Hello @Project2021 ,

You can try this to select which Month column value you want to return based on Age :

Switch(Age, 1, LookUp(Value, Name = ThisItem.Name, Month1), 2, LookUp(Value, Name = ThisItem.Name, Month2), 3, LookUp(Value, Name = ThisItem.Name, Month3), 4, LookUp(Value, Name = ThisItem.Name, Month4), 5, LookUp(Value, Name = ThisItem.Name, Month5))

Oh and if you use ; in the formula instead of using this ,
please change it to suit your format
I hope this helps!

Was this reply helpful? Yes No
Project2021 40 on at

Like (1)

Report

Thanks! This was exactly what I was looking for!

Was this reply helpful? Yes No
Verified answer

CNT 10,921 Super User 2024 Season 1 on at

Like (1)

Report
@Project2021 This way yo can have any number of months,
Formula for calculating the Current Price

"Current Price:" & LookUp(colValuePercent, Model=ThisItem.Model && MonthNumbner=ThisItem.Age).ValuePercent/100 * ThisItem.StartingPrice

Was this reply helpful? Yes No
Project2021 40 on at

Like (0)

Report

Thanks a bunch! with the help off you two I solved my problem!

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (1)

Report

@Project2021 Glad to help!

Please remember to give a 👍 and accept the solution as it will help others in the future.

Was this reply helpful? Yes No