Forum Discussion

Marc_N2R's avatar
Marc_N2R
New Member
10 years ago

2 dimensional lookup

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

 

2 Replies

  • elliotdixon's avatar
    elliotdixon
    Responsive Resident
    10 years ago

    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's avatar
      Marc_N2R
      New Member
      10 years ago

      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