LOOKUPVALUE and Filters

[Anonymous]

Hi, can you help me pls.

I can't find anything in this area

Еhere are 2 tables without any relationship.
I need to put the price from the first table to the second if Shop and Prod_Id matched.
But the problem is - that at the pricing table, we have different dates. and the date from the second table - should be >= maximum date - from the first. 

Thank you in advance.

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

“LOOKUPVALUE” function is not the right direction here. We can use a measure as below to work on it.

Price = 
VAR maxdate =
    CALCULATE (
        MAX ( Price[Date(when start work with this price)] ),
        FILTER (
            Price,
            Price[Shop] = Customer[Shop]
                && Price[Prod_id] = Customer[Prod_id]
                && Price[Date(when start work with this price)] < Customer[Date]
        )
    )
RETURN
    CALCULATE (
        MAX ( Price[Price] ),
        FILTER (
            Price,
            Price[Date(when start work with this price)] = maxdate
                && Price[Shop] = Customer[Shop]
                && Price[Prod_id] = Customer[Prod_id]
        )
)

Here’s a sample I made:

url: https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EYlU9eRqfdlPtjMm34AdjykBW3Om0nqXfSSkKF-MfSGaVA?e=EOZi6Y

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, @v-easonf-msft 

Thank you so much. It works perfectly. 
You rock!!!!