Forum Discussion

bilogin's avatar
bilogin
Icon for Helper I rankHelper I
6 years ago

Vlookup on concatenated value

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 c...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
bilogin
Icon for Helper I rankHelper I
6 years ago

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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