New Table using Latest Distinct Value

[newgirl]

Hi!

So I have this table that has consolidated list of client and the corresponding sales representative during the period.

Client	Sales Rep	YearMonth
A	Barney	202001
B	Ted	202001
C	Robin	202001
D	Lily	202001
A	Marshall	202002
B	Ted	202002
E	Rachel	202002

What I need is to create a new table that will consolidate unique values from the client field but show corresponding sales representative based on latest date.

Desired new table:

Client	Sales Rep
A	Marshall
B	Ted
C	Robin
D	Lily
E	Rachel

[Greg_Deckler]

@newgirl - Some something like:

Table = 
  VAR __Table =
      ADDCOLUMNS(
        DISTINCT('Table'[Client])
        "Latest",
        MAX('Table'[YearMonth])
      )
  VAR __Table1 =
      __Table,
      "Sales Rep",
      MAXX(FILTER('Table','Table'[Client] = [Client] && 'Table'[YearMonth] = [Latest]),'Table'[Sales Rep])
RETURN
  __Table1

[newgirl]

Hi, @Greg_Deckler !

I tried your measure although I think it lacked a certain DAX formula in the _Table1. I guessed it was ADDCOLUMNS so this is the measure I did:

Table = 
  VAR __Table =
      ADDCOLUMNS(
        DISTINCT('Table'[Client]),
        "Latest",
        MAX('Table'[YearMonth])
      )
  VAR __Table1 =
      ADDCOLUMNS(
      __Table,
      "Sales Rep",
      MAXX(FILTER('Table','Table'[Client] = [Client] && 'Table'[YearMonth] = [Latest]),'Table'[Sales Rep])
      )
RETURN
  __Table1

However, it returned a table wherein the Client values were indeed unique but in the column for Sales Rep, it shows one Sales Rep value that is the same across all rows.

[amitchandak]

@newgirl , Try like

lastnonblankvalue(Table[YearMonth],max(Table[Sales Rep]))

[newgirl]

Hi @amitchandak !

I tried your suggested formula in creating a new table but it says "The expression specified in the query is not a valid table expression".

I think it's also missing certain fields? Because in the desired table, I need the column for Client and another column for the Sales Rep. 

[DataZoe · Accepted solution]

@newgirl I think @amitchandak works if you create it as a measure, then in a table visual add the Client and the Measure.

Last Sales Rep = LASTNONBLANKVALUE('Table'[YearMonth],max('Table'[Sales Rep]))

If you want it as a seperate table, you could also do this:

Table 2 = SUMMARIZECOLUMNS('Table'[Client],"Last Sales Rep",[Last Sales Rep])

[newgirl]

Hi, @DataZoe !

Thank you so much for your suggestion. It worked perfectly!

[amitchandak]

@newgirl , for the new table try

summarize(Table, Table[Client], "Sales Rep",lastnonblankvalue(Table[YearMonth],max(Table[Sales Rep])))

That was a measure you can use in visual