Calculate the Latest Price from within previous 3 years for all items

[Anonymous]

Row No.	Zone	Item Code	Country	Year	Price	Benchmark Price (BP)
1	West EUR	A	Belgium	2015	-	-
2	West EUR	A	Belgium	2016	102	-
3	West EUR	A	Belgium	2017	104	102
4	West EUR	A	Belgium	2018	106	104
5	West EUR	A	Belgium	2020	110	106
6	West EUR	A	Netherlands	2019	120	106
7	West EUR	A	Netherlands	2020	122	120
8	West EUR	A	Netherlands	2020	124	120
9	West EUR	A	Netherlands	2021	124	124

Above table (from column "Row No." to column "Price") represents my dataset. I need help to calculate the "Benchmark Price". 

This is the logic for Benchmark Price (BP) -
BP is the "Latest Price within the last 3 years. This should be preferably from the same country but if not then at least from the same zone." If a purchase has been made within the last 3 years in the same country, then that price will be the BP. If a purchase has not been made within the last 3 years in the same country, then see if it has been purchased in at least the same zone. If yes, then that price will be BP (even though it might be from another country).

Explaining with examples-

Row No. 2 - There is no BP as item was not purchased in 2015 in Belgium and data before 2015 is not available. Item not purchased in 2015 in Netherlands (same zone) as well. 
Row No. 6 - The item was not purchased in Netherlands before 2019. However, we have a purchase made in the same Zone i.e. in Belgium. So BP will be the 2018 Belgium Price. (If 2018 Belgium Price wasn't available, then 2017 Belgium Price would have been the BP. If 2017 Belgium Price was also not available then 2016 Belgium Price would have been the BP. Only if none of 2018, 2017, 2016 prices were available in the entire Zone, then there would have been no BP. Even if 2015 Prices were available they would not have worked as they would be beyond the 3 year time frame.)

Row No. 9 - We have 2 prices for 2020 in the same country i.e. Netherlands. In such cases choose the higher price. 124 > 122 so BP is 124.

I have also done some color-coding to make it easier to understand where the BP is coming from.
Please let me know if there are any questions.
Would appreciate all help. Thanks in advance!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

BP measure : =

VAR currentcountry =

MAX ( Data[Country] )

VAR currentzone =

MAX ( Data[Zone] )

VAR currentyear =

MAX ( Data[Year] )

VAR samecountry_previousthreeyears =

FILTER (

ALL ( Data ),

Data[Country] = currentcountry

&& Data[Year] < currentyear

&& Data[Year] >= currentyear - 3

&& NOT ( ISBLANK ( Data[Price] ) )

)

VAR samecountry_maxofpreviousyears =

MAXX ( samecountry_previousthreeyears, Data[Year] )

VAR samezone_previousthreeyears =

FILTER (

ALL ( Data ),

Data[Zone] = currentzone

&& Data[Year] < currentyear

&& Data[Year] >= currentyear - 3

&& NOT ( ISBLANK ( Data[Price] ) )

)

VAR samezone_maxofpreviousyears =

MAXX ( samezone_previousthreeyears, Data[Year] )

VAR samecountry_BP =

GROUPBY (

FILTER (

samecountry_previousthreeyears,

Data[Year] = samecountry_maxofpreviousyears

),

"@BP", MAXX ( CURRENTGROUP (), Data[Price] )

)

VAR samezone_BP =

GROUPBY (

FILTER ( samezone_previousthreeyears, Data[Year] = samezone_maxofpreviousyears ),

"@BP", MAXX ( CURRENTGROUP (), Data[Price] )

)

RETURN

COALESCE (

IF (

HASONEVALUE ( Data[Row No.] ),

SWITCH (

TRUE (),

NOT ( ISBLANK ( samecountry_BP ) ), samecountry_BP,

NOT ( ISBLANK ( samezone_BP ) ), samezone_BP

)

),

" "

)

[Anonymous]

Thanks a lot for providing the solution 🙂 ! Quick follow up. I need to add 2 more columns:
1. BP Year (This tells us from which year the BP was taken)
2. BP Country (This tell us from which country the BP was taken)

For example in above data set for Row No. 6 -
BP = 106
BP Year = 2018
BP Country = Belgium 

Appreciate your help ! Thanks again ! 

[Anonymous]

@amitchandakrequesting assistance sir !