Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
You are welcome.
Why are there two 1's against Basic salary?
Because the last basic salary starts in 2014 and I want the latest record of each allowance type against each year.
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.
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |