New column with last occurrence data based on multifactor

[lucasmnx]

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.

[Greg_Deckler · Accepted solution]

@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

[lucasmnx]

Many Thanks @Greg_Deckler !! Worked perfectly!