Forum Discussion

samdep's avatar
samdep
Advocate II
5 years ago

Filtering for Max Date by Customer, then Retrieving Task/Subject (Field Name) by Customer/Date

Hey Everyone!

 

I have three fields in my table -- Activity Date, Task, Client ID. I created a conditional column to pull in the last date of activity associated with each client - expression below:

 

LAST ACTIVITY DATE =
VAR CLIENT_ID = Task[Case.ClientID]
RETURN
CALCULATE(MAX(Task[ActivityDate]),
FILTER(Task,
Task[Case.ClientID] = CLIENT_ID))

 

This works perfectly, retrieving the last activity date associated with each client_id... but, how do I expand on this to pull in the last task/subject tied to both that client_id and MAX(Task[ActivityDate]). The below is giving me one task/subject, so I am assuming it's not looking by both client_id and max[activitydate]... just by max[activitydate].

 

LAST TASK =
VAR MAXDATE =
CALCULATE(
MAX(Task[ActivityDate]),
FILTER(Task, Task[Case.ClientID]))

RETURN
LOOKUPVALUE(Task[Subject],
Task[ActivityDate], MAXDATE,
Task[Case.ClientID]
)

 

Sample Data:

 

Client ID    Task          Date

1234          Initial        1/12/21

1234          Connect    4/20/21

 

Goal Output in Table:

 

Client ID   Task           Date

1234         Connect     4/20/21

 

*Capturing the ClientID, the latest activity date associated with the client & the task/subject associated with both.

 

Thank you in advance!! 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    // The calculation in the column you're doing
// is sub-optimal since it uses CALCULATE in each
// and every row; this should be avoided for memory
// and speed reasons. Please use the code the below.

[Last Activity Date] = // calculated column
var vClientID = Task[Case.ClientID]
var Result =
    MAXX(
        Filter(
            Task,
            Task[Case.ClientID] = vClientID
        ),
        Task[ActivityDate]
    )
RETURN 
    Result
    
    
[Last Subject] = // calculated column
var vClientID = Task[Case.ClientID]
var vLastActivityDate = Task[Last Activity Date]
var Result =
    MAXX(
        FILTER(
            Task,
            Task[Case.ClientID] = vClientID
            &&
            Task[Last Activity Date] = vLastActivityDate
        ),
        Task[Subject]
    )
RETURN
    Result
    • samdep's avatar
      samdep
      Advocate II
      5 years ago

      Anonymous - Thank you so much for the advice on CALCULATE - didn't realize.

      Last Activity Date works perfectly - It is pulling in the Latest Date Stamp for Client Activity.

       

      The Subject is not quite working though -- In one scenario I looked at, the last date of activity is 3/31/21 - Outreach and the Task Subject is pulling in Session 5 (which occurred on 2/17/21) -- and the only reason I can think that this might be happening is because there were two subjects that took place on 2/17/21 ("Session 5" and "Resourcing 5") -- and the expression is returning one of the subjects from 2/17/21 instead of the latest on 3/31/21.

       

      Any thoughts? Thanks!