Obtaining text value from last transaction per customer

[tvcooper]

I have a pretty simple model.  I have a SALE table, a Customer Table, a Code Table and a Calendar.  All one to many relationships.

Customer table: (subcustnum = sale[subcustnum])

CustomerNum     Customer Name          SubCustomerNum      SubCustName

1                          Smith                           123                               Mary

1                          Smith                           128                               Bob

2                          Jones                            114                               Hortence

CODE table:   (Code = Sale[Transcode])

Code            CodeGroup

CD                Delivery

CF                 Delivery

FP                 Service

TT                 Service

LG                 Other

SALE Table:

Customer Num     SubCustomer                 Transaction Num     TransCode     TransDate      TransSeq

1                                   123                           12354                      CD                  3/1/22              1

1                                   128                            88432                      FP                  4/8/23              3

1                                   123                            63952                      LG                  4/8/23              2

2                                    114                           33684                       TT                  5/1/20              1

2                                    114                           55532                        CD                6/8/22               2

I want to pull the CodeGroup from Code table  for the last transaction for each customer from sale table.  Result should look like this:

Customer Name           TransDate                CodeGroup

Smith                             4/8/23                    Service

Jones                              6/8/22                   Delivery

My formula is:

LastCode =
VAR MaxSeq = Max(Sale[TransDate])

VAR CodeName = Calculate(Max(Code[group]),Sale[TransDate]>=MaxSeq

Return

CodeName

That resulted in the error cannot convert value 'deliver' of type text to type number.

So I modified from max(code[group]) to values(code[group]) and got "a table of multiple values was supplied where a single value was expected"

So to test the parts,I returned "MaxSeq" (instead of Code Name).  Weirdly, I got a negative value.  The number was correct, but all the numbers in seq are positive, so I don't understand why I got negative numbers.  (THIS IS QUESTION ONE).

Question two -and my main question, is how do I pull this darn code??

[tvcooper]

@Greg_Deckler  Thank you for the help.  This worked great.  I'm dealing with another issue where my sequence isn't unique.  (Its a calculated column and I don't have enough unique markers to derive a unique sequence).  In the case where my sequence isn't unique, I"m getting blank results.  (I can't use date because date isn't unique and in some cases isn't the order of the transactions.

[Greg_Deckler · Accepted solution]

@tvcooper Try:

LastCode =
  VAR __CustomerNum = MAX('Customer'[CustomerNum])
  VAR __MaxSeq = Max(Sale[TransDate])
  VAR __TransCode = MAXX(FILTER('Sale', [Customer Num] = __CustomerNum && [TransDate] = __MaxSeq), [TransCode])
  VAR __Result = MAXX(FILTER('CODE', [Code] = __TransCode), [CodeGroup])
RETURN
  __Result