calculate data from other tables and from the same column in process

[nicolasvc]

I have a table with the price of a product per year and month, I also have another which I pivoted to have the growth percentage of each product per year. And I need to form a table in which I have the prices of the product but with the percentage increase. The problem is that for the first year, 2022, I have to calculate it based on the first table, but then for 2023 and those that follow, I have to start from the previous year, which would be 2022 in the case of 2023, and so on.
And I don't know how to calculate 2022 by obtaining the data from the other tables, and even less the ones that follow.

year	month	product	price
2021	1	A	2000
2021	1	B	1000
2021	2	A	1000
2021	2	B	2010
...	...	...	...

product	year	percentage
A	2022	1.4%
B	2022	2.2%
A	2023	3.1%
B	2023	1.4%

And I need to form a table in which I have the prices of the product but with the percentage increase. The problem is that for the first year, 2022, I have to calculate it based on the first table, but then for 2023 and those that follow, I have to start from the previous year, which would be 2022 in the case of 2023, and so on.
And I don't know how to calculate 2022 by obtaining the data from the other tables, and even less the ones that follow.

disclaimer: the values that are there are not correct

year	month	product	price
2022	1	A	2500
2022	1	B	1500
...	...	...	...
2023	1	A	2500

[v-henryk-mstf]

Hi @nicolasvc ,

Try the way @AlexisOlson  gave to see if it works. If there is still a problem, please provide details of the confusion and I will answer it for you as soon as possible.

Looking forward to your reply.

Best Regards,
Henry

[AlexisOlson · Accepted solution]

You measure might look something like this:

Predicted Price =
VAR CurrYear = SELECTEDVALUE ( DimDate[Year] )
VAR CurrMonth = SELECTEDVALUE ( DimDate[Month] )
VAR CurrProduct = SELECTEDVALUE ( Prices[product] )
VAR StartPrice =
    LOOKUPVALUE (
        Prices[price],
        Prices[product], CurrProduct,
        Prices[month], CurrMonth
    )
VAR GrowthFactor =
    PRODUCTX (
        FILTER ( Growth, Growth[product] = CurrProduct && Growth[year] <= CurrYear ),
        1 + Growth[percentage]
    )
RETURN
    StartPrice * IF ( CurrYear = MIN ( Prices[year] ), 1, GrowthFactor )

Details will vary but the key logic is that you use StartingPrice * ( 1 + % ) * ( 1 + % ) where you have one % value for each year beyond the starting year.

[AlexisOlson]

That likely means that LOOKUPVALUE is not finding a single unique value (or else CurrProduct or CurrMonth is blank).

It works when I set up the example data

Your data may have additional complexity that I didn't anticipate (since I can't see it...). My suggested measure was just meant to demonstrate the basic logic. It's very likely you'll need to adapt it to your particular situation.

[nicolasvc]

thanks for the answer, but for some reason the StartPrice variable is always empty 😞

[AlexisOlson]

So the price for A in month 1 of 2022 would be 2000 * 1.014 and 1000 * 1.014 for month 2 of 2022?

[nicolasvc]

Yes, exactly.