Forum Discussion

lucasmnx's avatar
lucasmnx
Frequent Visitor
3 years ago
Solved

New column with last occurrence data based on multifactor

Hi,

 

I have a database with sales from past 5 years and every salespersons  on the company, naturally the same clients have differents salespersons from time to time, what I want to do is create a new column to keep the latest sales person to every sales based on sales week, client name and agency name.

 

I'm not sure if its clear so below is a example from what I expect as result.

 

I already tried to use a combination from different functions such as IF, EARLIER, SEARCH and tried to do this in more than one column (step by step) and have no success in any of my tentatives.

 

 

 

Thanks in advance.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    lucasmnx Try:

    Column =
  VAR __Client = [Client]
  VAR __Agency = [Agency]
  VAR __Week = [Sales Week]
  VAR __Table = FILTER(ALL('Table'), [Client] = __Client && [Agency] = __Agency)
  VAR __MaxWeek = MAXX(__Table, [Sales Week])
  VAR __Vendor = MAXX(FILTER(ALL('Table'), [Client] = __Client && [Agency] = __Agency && [Sales Week] = __MaxWeek), [Vendor Name])
RETURN
  __Vendor

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    lucasmnx Try:

    Column =
  VAR __Client = [Client]
  VAR __Agency = [Agency]
  VAR __Week = [Sales Week]
  VAR __Table = FILTER(ALL('Table'), [Client] = __Client && [Agency] = __Agency)
  VAR __MaxWeek = MAXX(__Table, [Sales Week])
  VAR __Vendor = MAXX(FILTER(ALL('Table'), [Client] = __Client && [Agency] = __Agency && [Sales Week] = __MaxWeek), [Vendor Name])
RETURN
  __Vendor