Comparing two values in one column

[Kopek]

Hi, I am having a strange isssue comparing two values from one column, one table.
Below you will find an example of my datasource.
I want to check if employee id in May = Employee id in April, and in this case for ID 6666 i want to obtain "NS", for ID 7777 "Leaver".

I have prepared few functions but it always gives me false result, meaning that it always says all ids are different.
The employee ID is a type text, i have converted it to numbers (not ideal...) but have not worked either...
Could you pleasehelp me and let me know what i have done wrongly ?

Date	Employee ID
01 May 2021	1111
01 April 2021	1111
01 May 2021	2222
01 April 2021	2222
01 May 2021	3333
01 April 2021	3333
01 May 2021	4444
01 April 2021	4444
01 April 2021	5555
01 May 2021	5555
01 May 2021	6666
01 April 2021	7777
01 May 2021	8888
01 April 2021	8888
01 May 2021	9999
01 April 2021	9999

I have tried following as a measure and as a column but the outcome alway was false...

Measure = 

Var MaxDate = MAX('People reports'[Source.Name.1])

Var EIDCurrent = CALCULATE(MAX('People reports'[Employee ID]),'People reports'[Source.Name.1] = MaxDate)

Var PrevMonth = MIN('People reports'[Source.Name.1])

Var EIDPrevMth = CALCULATE(MAX('People reports'[Employee ID]),'People reports'[Source.Name.1] = PrevMonth)

Return

IF(EIDCurrent = EIDPrevMth,0,1)
have also tried added new flags, different measures but nothing work...

Thanks in advance!

[Jihwan_Kim · Accepted solution]

https://www.dropbox.com/s/5h0r4sm9xp268yv/kopek.pbix?dl=0 

[v-rzhou-msft · Accepted solution]

Hi @Kopek 

You need to create two Employee ID List in your measure and compare your values with the List in your measure.

Try this measure:

Measure = 
VAR _MAXDate = MAXX(ALL('People reports'),'People reports'[Date])
VAR _MINDate = MINX(ALL('People reports'),'People reports'[Date])
VAR _MaxPeopleList = SUMMARIZE(FILTER(ALL('People reports'),'People reports'[Date] = _MAXDate),'People reports'[Employee ID])
VAR _MinPeopleList = SUMMARIZE(FILTER(ALL('People reports'),'People reports'[Date] = _MINDate),'People reports'[Employee ID])
RETURN
SWITCH(TRUE(),AND(MAX('People reports'[Employee ID]) in _MaxPeopleList,MAX('People reports'[Employee ID]) in _MinPeopleList),"In Two Month",AND(MAX('People reports'[Employee ID]) in _MaxPeopleList,NOT(MAX('People reports'[Employee ID])) in _MinPeopleList),"NS","Leaver")

Result:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Jihwan_Kim · Accepted solution]

https://www.dropbox.com/s/5h0r4sm9xp268yv/kopek.pbix?dl=0