Compare lists- New and Lost Employees Highlighted

[bw70316]

I am trying to compare monthly employee lists and create a list highlighting new and let go employees. I was able to append the monthly datasets and use conditional formatting to highlight individuals that only appeared once. However, this highlighted BOTH new and fired employees in the SAME color (since they only appear once each).  I'd like a way to delineate between who is a new employee and who has moved on from the company. Unfortunately, this is on my work computer so I can't give you a screenshot. Below is an example of what I'm shooting for:

Employees

1. Bob

2. Daniel

3. Smith

4. Neo

5. Morpheus

New Employees:

4. Neo

5. Morpheus

Old Employees 

2. Smith

New Combined List:

1. Bob

2. Daniel

3. Smith

4. Neo

5. Morpheus

[v-lili6-msft · Accepted solution]

hi,@bw70316

    After my research, you can do these follow my steps as below:

Step1:

Assuming that this is this month table

and this is last month data

then I append the last month data

Step2:

Add count column

Count = CALCULATE(COUNT(Append1[Name]),FILTER(Append1,Append1[Name]=EARLIER(Append1[Name])))

Step3:

Use lookupvalue function to search the name in last month data table

last Mon = LOOKUPVALUE(Table2[Name],Table2[Name],Append1[Name])

Step4:

Use this formula :

this Mon Emp = IF(Append1[last Mon]=BLANK()||Append1[Count]>1,Append1[Name])

Result:

Now the rows that count=1 are New Employees  and count>1 are old Employees 

and this Month Emp is blank are Lost Employees.

Best Regards,

Lin

[bw70316]

Thank you very much, worked on the very first try. May I ask what avenue you took to learn DAX like this... youtube, udemy?