Lookup Values across 2 Tables based on several conditions

[awolf88]

Dear geniusses,

Having some issues again and was hoping you could kindly point me in the right direction once again:

I have 2 Tables: one with Sales (blue table) and one with Product Prices (orange) and according dates, from when prices were valid from and valid to. 

On a very basic level in PowerBI, I want to get the according Unit Price from product price Table (orange) into the blue one, correlating on what the price should have been (orange) to when it was sold (blue). 

I was able to accomplish this via a calculated column, Code reading as follows: 

That was the easy part. 

Now there is one more parameter i need to factor in: there is a price reduction (in the orange list), depending on the volume customers purchase at. So the extended list would now look as such: 

So what i would need in the code is for it to not only look up the date the price was valid in, but also at what quantity it should relate to. As soon as I now add the several "at volume" options in PowerBI, i keep getting an understandable #ERROR message. 

Looking forward to your suggestions as always, team!

Thanks in advance and best regards,

Alex

[v-cgao-msft · Accepted solution]

Hi @awolf88 ,

Try this.

Price by Salesdate =
VAR _quantity = 'SalesTable'[Quantity]
VAR _at_volumn =
    SWITCH (
        TRUE (),
        _quantity > 0
            && _quantity < 300, 0,
        _quantity > 300
            && _quantity < 500, 300,
        _quantity > 500, 500
    )
VAR _price =
    CALCULATE (
        VALUES ( Listpreise[Unit Price] ),
        FILTER (
            'Listpreise',
            'SalesTable'[Product] = 'Listpreise'[Product]
                && 'SalesTable'[Sales Date] >= 'Listpreise'[Valid form]
                && 'SalesTable'[Sales Date] <= 'Listpreise'[Valid to]
                && 'Listpreise'[at Volume] = _at_volumn
        )
    )
RETURN
    _price

Attach the PBIX file for reference. Hope it helps.

If this doesn't work for you or I misunderstand your needs, please consider sharing more details about it.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar.
2. Expected output from sample data.
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[awolf88]

Dear community team,

I appreciate all you help in this matter! I was able to modify the Swich code to my needs and adapt it for the entries, that do not all have a value for each bracket. 
See my adaptation below to hopefully help out anybody else in need of solution for this matter: 

Thanks and again i appreciate everybody's input I've received. This platform truly has community character and helps each other out!

Best regards to everyone,

Alex

[awolf88]

Dear Community Support Team,

appreciate your response! Your suggestion totally did the trick for the very simple sample data. When I tried to roll it over into my main Data file, I've noticed one more issue I wasn't aware about myself, but maybe you have an idea for a solution as well: 

It turns out that not all products have entries / positions for "at volume" quantities: I've adjusted the "Listpreise" column for the Product "Fruit" for reference: so in our example there is no "at volume" discount for Fruit when purchased. 

What happens within PowerBI with the code is that all entries within the "Price by Salesdate" remain blank rather than referencing the "at quantity" - "0" value. 

Is there an easy solution this as well? 

Thank you so much for your effort and time. I really appreciate your help in this matter.

Best,

Alex

[Daz4]

Quantity2 = IF(ATVOLUME=0,0,IF(ATVOLUME=500&&IF(QUANTITY>=500,500, IF(ATVOLUME=300&&IF(Quantity >=300,300,0)))))

Something like this might work better then. You may want to create a ATVOLUME column in the second table also, based on Product and date. 

ATVOLUME = LOOKUPVALUE(ATVOLUME,

[Daz4]

You are missing the statement to filter the quantity. 

&&SalesTable.[Quantity]=ListPreice.[AtVolume]

However, this would also give you multiple results in most cases. You can create a new calculated column in the Sales Table similar to the oen below, and replace Quantity with Quantity2 in the above formula.

Quantity2 = IF(Quantity >=500,500, IF(Quantity >=300,300,0))

[awolf88]

Hi Amit,

appreciate your response. Using min/max/sum/count as you say totally makes sense. 

I'm still having trouble for it aggregating the correct values though as my issue is based on 2 Parameter for it to aggregate - one being the date between "Valid From" / "Valid To", and second making sure it takes the correct price according to quantity ordered "at Volume".

Thanks!

[amitchandak]

@awolf88 , In place of values, use Min/Max/Sum/Count etc in line 3

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8