Tableau Get the Max Date for each employee based on one condition

Question

I'm new to Tableau. I ran into a challenge and cannot find solution online.

I have a dataset about employee's task completion KPI. For each employee, he/she gets a list of assignment items. Each assignment item has the assignment date. If the employee finish this assignment, the task status will be "Completed". Otherwise, for any unfinished assignments, the task status will be "Pending".

I would like to know for each employee, if the Task Status is "Completed", what's the most recent Assignment Date.

I created a calculation field in Tableau, but never get my calculation valid. It would be much appreciated for any suggestions or solutions. My code and sample data are as below:

{FIXED [Employee]: IF [Task Status]="Completed" THEN MAX([Assignment Date]) END}

OR

MAX({FIXED [Employee]: IF [Task Status]="Completed" THEN [Assignment Date] END})

Employee	Assignment Date	Task Status
A	8/1/2023	Completed
A	8/4/2023	Completed
A	8/5/2023	Pending
A	8/9/2023	Completed

Answer 1

I believe the syntax will be as follows:

{ FIXED [Employee] : MAX( IF [Task Status] = "Completed" THEN [Assignment Date] END ) }

note you need to place the condition(s) inside the aggregate function, but not the FIXED item(s)