Create calculated column with measure conditions using DAX

[POSPOS]

Hi all,

I have a sample data as below

Sample file is attaced here.

I have created three measures, to highlight the employees who has only "FY 24"and only "FY 25" values.

Measure 1 :
FY 24 = CALCULATE(COUNT('Sample data'[Employee Name]),'Sample data'[FY]="FY24")
Measure 2:
FY 25 = CALCULATE(COUNT('Sample data'[Employee Name]),'Sample data'[FY]="FY25")
Measure 3:
Indicator 24_25 = 
[FY 24]-[FY 25]

Now, I am trying to create a caluclated column in such a way that
if a employee has both FY 24 and FY 25 then show as "Both",
if only FY24 then show as "dropped from FY24"
if only FY 25 then "added in FY25".

I tried to create a cal column using the Measure 3 I created, but does not give expected results.

Can someone please help in providing a solution on how to achieve this using DAX.

Thank you

[POSPOS]

@ArwaAldoud  - Thank you for providing this solution. It worked!

[ArwaAldoud]

You're welcome @POSPOS  Glad to hear that it worked.

[ArwaAldoud · Accepted solution]

Hi @POSPOS 

try this 

FY_Status =
VAR HasFY24 = CALCULATE(COUNTROWS('Sample data'), 'Sample data'[FY] = "FY24", ALLEXCEPT('Sample data', 'Sample data'[Employee Name])) > 0
VAR HasFY25 = CALCULATE(COUNTROWS('Sample data'), 'Sample data'[FY] = "FY25", ALLEXCEPT('Sample data', 'Sample data'[Employee Name])) > 0

RETURN
SWITCH(
TRUE(),
HasFY24 && HasFY25, "Both",
HasFY24, "Dropped from FY24",
HasFY25, "Added in FY25",
"Unknown"
)