Looking up values based on two variables

[slav84]

Hello, 

I have two simple tables. In Transaction table I have SKU number and date when the SKU was purchased. 
In my Refference  table I have SKU and date when the SKU Testing went live. 
What I need is in new column in Transaction table that wil say Live/Not Live. So I basically i have to look up the SKU and Purchase date from Transaction table and compare it to SKU and DateLive date in Refference table. And if the Date of Purchase is = or greater than DateLive, than I will get value "Live" In column WasItLive. Otherwise if the date of purchase was less then When the SKU testing DateLive then I want to get "Not Live" value. 

So i need to know if sku was testing or not at the time of purchase. 

Another thing is once the SKU is live for testing it stays live which is why i think i have to use >= when comparing purchase date vs DateLive. 

I have been stuck on this problem for few hours. It seems simple but every fucntion i tried is not giving me correct results. 

Any Idea?

Transactions

SKU	PurchaseDate	WasItLive
123123	1/1/2022
321453	1/3/2022
413567	1/5/2022
326789	1/14/2022
908687	1/7/2022
567493	1/18/2022
234236	1/2/2022
583467	1/15/2022
714267	1/25/2022
762345	1/9/2022

Refference

SKU	DateLive
123123	1/1/2022
321453	1/7/2022
413567	1/13/2022
326789	1/19/2022
908687	1/25/2022
567493	1/31/2022
234236	2/6/2022
583467	2/12/2022
714267	2/18/2022
762345	2/24/2022

[v-rongtiep-msft · Accepted solution]

Hi @slav84 ,

Please refer to my pbix file to see if it helps you.

Create a column.

Column =
VAR datelive =
    LOOKUPVALUE ( Refference[DateLive], Refference[SKU], Transactions[SKU] )
RETURN
    IF ( Transactions[PurchaseDate] >= datelive, "Live", "Not live" )

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rongtiep-msft · Accepted solution]

Hi @slav84 ,

Please refer to my pbix file to see if it helps you.

Create a column.

Column =
VAR datelive =
    LOOKUPVALUE ( Refference[DateLive], Refference[SKU], Transactions[SKU] )
RETURN
    IF ( Transactions[PurchaseDate] >= datelive, "Live", "Not live" )

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[slav84 · Accepted solution]

This will work. Thank you so much

[v-rongtiep-msft]

Hi @slav84 ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

If your Reference table only contains unique values, i.e. 1 row per SKU, then you can create a 1-to-many relationship between the tables and use the RELATED function to add a calculated column to the Transactions table, e.g.

Was live = IF( Transactions[Purchase Date] > RELATED('Reference'[Date Live]), "Yes", "No")

[slav84]

Is there any other way without doing relationship between these two tables?

[johnt75]

You could use LOOKUPVALUE but that will fail if it returns more than 1 value, so you really need the SKUs to be unique