Forum Discussion
Looking up values based on two variables
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 |
Hi Anonymous ,
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.
6 Replies
Hi Anonymous ,
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.
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")You could use LOOKUPVALUE but that will fail if it returns more than 1 value, so you really need the SKUs to be unique
