How to filter Termination date(last date)

[MahamoodBi]

Hi Power bi experts,

I need to find out Termination date in below table,i have 3 records (i.e Action Type - D1, EE,ES this are employees last date )
i need these three data in the separate Termination Column, could you please suggest a calculated column and  also need to aplly filter Action Type {"D1", "EE","ES"}

my output will like

[tamerj1 · Accepted solution]

@MahamoodBi 

Please try

Termination Date =
VAR CurrentDate = TableName[Action Date]
VAR TerminationDate =
    CALCULATE (
        MAX ( TableName[Action Date] ),
        ALLEXCEPT ( TableName, TableName[Emp.No] ),
        TableName[Action Type] IN { "D1", "EE", "ES" }
    )
RETURN
    IF ( CurrentDate = TerminationDate, TerminationDate )

[MahamoodBi]

@tamerj1  i am getting error in below code

could you please suggest.

[tamerj1]

@MahamoodBi 
Seems you have two fact tables. Please provide details about yuor data model and relationships.

[MahamoodBi]

Thank you @tamerj1  ,now dax functions is working.