Filter the latest record based of multiple fields

[Anonymous]

Hi Everyone,

I am Posting this question second time and expecting you guys to help me in this regard.

Emp_Name	Allowance Type	strt date	end date	Amount		Flag
10234	House Rent	3/1/2013	6/30/2013	4000		0
10234	Social Allowance	3/1/2013	5/30/2013	5000		0
10234	House Rent	7/1/2013	2/28/2014	6000		1
10234	Basic Salary	2/1/2013	9/30/2013	14000		0
10234	Basic Salary	10/1/213	5/30/2014	15000		1
10234	School Allowance	4/1/2013	3/20/2015	3000		1
10234	Basic Salary	6/1/2014	7/30/2014	17000		0
10234	Basic Salary	8/1/2014	11/30/2014	19000		1

I have list of records and i want to add a column that mark the latest record as 1 and old as 0 as described below
As you see in the attached screenshot there are number of records against 10234,it has difference allowance types with start and end date of each allowance and amount aswell.
You can see that his  base salary and and house rent changes multiple time in year 2013(start date) and in 2014 only basis salary changes.I want a flag with 1 for lastest record base on employee,allowantype and strt date and for the remaining one put 0 so that i can filter the records.


Thanks

[Anonymous]

Thanks  alot @Ashish_Mathur ,that works for me.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Why are there two 1's against Basic salary?

[Anonymous]

Because the last basic salary starts in 2014 and I want the latest record of each allowance type against each year.

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

Year of start date = YEAR(Data[strt date])

Flag = if(Data[strt date]=CALCULATE(MAX(Data[strt date]),FILTER(Data,Data[Emp_Name]=EARLIER(Data[Emp_Name])&&Data[Allowance Type]=EARLIER(Data[Allowance Type])&&Data[Year of start date]=EARLIER(Data[Year of start date]))),1,0)

Hope this helps.