Vlookup on concatenated value

[bilogin]

Hi everyone,

Thanks in advance for helping me out. 

I have all the sales data from the last 2 years in one table and I need to see whether the sale is new business or not.

New biz = new customer, new product OR existing customer, new product OR existing customer, existing product if not bought in last 2 years. 

The columns needed for the formula are:

Account Number // Part // Perd // Transaction Date // Concat

                                           Perd = Period. Our year runs Oct-Sep so 2001 would be October 2019, 2002 is November 2019 and so forth. I've tried to think about how to utilise this but I think Transaction Date-730=TODAY() might be easier but I couldn't get that to work either.

I've already concatenated the account number and the part to create a unique value to search on, however I've found that LOOKUPVALUE doesn't work on the Concat column. 

If using Excel, I would use a snazzy IF & VLOOKUP formula but I'm not sure how it would work in BI. 

Not sure if this is relevant but the data is coming from a folder containing Excel spreadsheets that will be updated with new data each month. This is a temporary fix until a gateway to the original source has been established.

Thanks,

Bec

[Anonymous]

The sytax of LOOKUPVALUE is this

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

LOOKUPVALUE(what are you looking for, where you should search for, which column you extract)
You are searching the [date] in the [concat] column and try to extract the [concat] , which makes no sense.

[bilogin]

@Anonymous , according to the link, the first bit is what you want to return so I'm a bit confused by your message.

I do want to return the transaction date as I then need to see when the product was last sold. 

[amitchandak]

Can two columns like this help? Create a new columns

last sales = maxx(filter(sales,sales[Account]=earlier(sales[Account]) &&  sales[date]<earlier(sales[date])),sales[date])
 
 last sales of same product == maxx(filter(sales,sales[Account]=earlier(sales[Account]) && sales[part]=earlier(sales[part]) &&  sales[date]<earlier(sales[date])),sales[date])

 If the last date is null it new if date diff last date and date > 2, then it can be new, etc

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[bilogin]

@amitchandak , it seems that the first column didn't work after all. It pulled in a date but it wasn't the correct date.

I think that eventually the earlier function may be the one that I end up going with however I don't understand where I'm going wrong with the syntax so I'll youtube some videos then come back to this thread. 

[amitchandak]

Can you paste data in table format. I will provide formula based on that.

[Anonymous]

As a general suggestion, avoid using EARLIER. The same result can be obtained using a VAR and it's more readable.

So from this

Last sales of same product = maxx(filter('New Biz',[6 digit Account number]=earlier('New Biz'[6 digit Account number])
			&&[Part]=earlier('New Biz'[Part])
			&&'New Biz'[Trn Date]<earlier('New Biz'[Trn Date])),'New Biz'[Trn Date])

(by the way this formula contained a small mistake, fixed below) 

to this

Last sales of same product = 
var currentPart='New Biz'[Part]
var currentTrnDate='New Biz'[Trn Date]
var currentAccountNr=earlier('New Biz'[6 digit Account number])

RETURN
maxx(filter('New Biz','New Biz'[6 digit Account number]=currentAccountNr
			&& 'New Biz'[Part]=currentPart
			&& 'New Biz'[Trn Date]<currentTrnDate),'New Biz'[Trn Date])

 I'm not debugging the formula, I don't know if it works or not, it's just a suggestion. EARLIER is harder to understand , even experts tend to avoid it.

[bilogin]

@amitchandak it looks like the first column has worked, thank you.

I'm getting an error on the second column, too many arguments were passed to the FILTER function. The maxium argument count for the function is 2:

Last sales of same product = maxx(filter('New Biz',[6 digit Account number]=earlier('New Biz'[6 digit Account number])&&[Part]=earlier('New Biz'[Part])&&'New Biz'[Trn Date]<earlier('New Biz'[Trn Date]),'New Biz'[Trn Date]))

What am I missing here? 

[amitchandak]

Try like

Last sales of same product = maxx(filter('New Biz',[6 digit Account number]=earlier('New Biz'[6 digit Account number])
			&&[Part]=earlier('New Biz'[Part])
			&&'New Biz'[Trn Date]<earlier('New Biz'[Trn Date])),'New Biz'[Trn Date])

[Anonymous]

Yeah, you're right.

But anyway, you're having the same value for <search_columnname> and <search_value>. 
The last parameter should be what you are searching.

Something like

Lookup=LOOKUPVALUE([date column],[Concat],<and here put what you're looking for in concat>)