Need to create new column as Max latest record per employee as one otherwise Zero

[Venkateswara_ra]

Hi All,

Need to create a new column as max latest reocrd per employee as 1 or Zero

if the emp having only one record than its 1

Can anyone help me 

My data looks like 

Emp id	date	New column output
45	7/15/2020	0
45	9/2/2020	0
45	9/4/2020	1
25	2/05/2020	0
25	8/4/2020	1
36	11/14/2019	1
36	11/1/2019	0
75	11/25/2019	1
75	11/18/2019	0
48	8/22/2020	1
22	7/22/2020	1

[pranit828 · Accepted solution]

HI @Venkateswara_ra 

Create a column 

_max = CALCULATE (max(('Table'[Date]),ALLEXCEPT('Table','Table'[EMP ID]))

Then use the below

if(CALCULATE(COUNTROWS('Table'),'Table'[Date]=_max)) >0,1,0)

[v-easonf-msft · Accepted solution]

Hi, @Venkateswara_ra 

Try  to create a   column  to calculated the max date for per employee first:

Max_date = CALCULATE(MAX('Table'[date]),ALLEXCEPT('Table','Table'[Emp id]))

Then create a calculate column as below:

Column = IF('Table'[date]='Table'[Max_date],1,0)

If the date of this record equal Max_date ,the result will show "1"

The result will show as below:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Venkateswara_ra , Try as new column

max status

new column = maxx(filter(Table, [Emp id] = earlier([Emp id])),[curretStatus])

status on max date

new column = maxx(filter(Table, [Emp id] = earlier([Emp id])),lastnonblankvalue([date],max([curretStatus])))

[Venkateswara_ra]

@amitchandak 

Thanks for reply

Here in the table i need to create currentstatus column as max date per employee as 1 remaining date as zero  

[pranit828 · Accepted solution]

HI @Venkateswara_ra 

Create a column 

_max = CALCULATE (max(('Table'[Date]),ALLEXCEPT('Table','Table'[EMP ID]))

Then use the below

if(CALCULATE(COUNTROWS('Table'),'Table'[Date]=_max)) >0,1,0)

[pranit828]

Hi @Venkateswara_ra 

if(CALCULATE (
COUNTROWS('Table'),
FILTER ( ALLEXCEPT('Table','Table'[EMP ID]),'Table'[Date]=MAX([Date]))
) >0,1,0)

[Venkateswara_ra]

@pranit828 

Thanks for reply

I tried your logic but its taking max date in the table

but my requriement it needs to take maxdate per employee as 1 others dates of that employee as zero