2 dimensional lookup

[Marc_N2R]

Hi,

For more than a year now I get every day activity reports from my customer with different type of activities all having a different price. I have a separate price list with a relationship to those activities. The first of March now the prices per activity are going to change. The challenge I am heading is how to keep all activities before March 2016 at the old price and to change all activities after to the new price.

Is there anyone who had this challenge before?

Many thanks for your help

[elliotdixon]

hi @Marc_N2R

do you have some tables and examples of data. Could look at the problem with a better idea of what you are trying to achieve then.

Cheers

ED

[Marc_N2R]

Hi Eliot,

I get every day a dump from a customer system which we use to invoice. The dump (More than 2 years of history) has a couple of columns of which I use 2 (Service Category, date). Since the price per service category changes yearly (see table below) I wonder I how dynamically can define the price base on the date. In Excel I did this with "vlookup" and "Match" but don't know how to best do this in PBI.

Many thanks for you help

Marc

Service Category	01/04/2014	01/04/2015	01/04/2016
Cat 1	 		90.00 	 	88.20 	 	87.32 
Cat 2	 		70.00 	 	68.60 	 	67.91 
Cat 3	 		60.00 	 	58.80 	 	58.21 
Cat 4	 		50.00 	 	49.00 	 	48.51 
Cat 5	 		75.00 	 	73.50 	 	72.77 
Cat 6	 		85.00 	 	83.30 	 	82.47 
Cat 7	 		95.00 	 	93.10 	 	92.17 
Cat 8	 		40.00 	 	39.20 	 	38.81 
Cat 9	 		30.00 	 	29.40 	 	29.11 
Cat 10	 		20.00 	 	19.60 	 	19.40