Hello,
I am new to PowerPivot and learning by hit and miss.
I have the following table.
DeptName EmpCode EmpName StartDate EndDate
Dept A UK001 John 1/1/2013 3/30/2013
Dept B UK001 John 4/3/2013 5/4/2013
Dept A UK001 John 5/5/2014 11/15/2014
Dept C UK001 John 11/19/2014 2/7/2015
Dept D UK001 John 2/15/2015 12/30/2017
Dept B UK002 Jane 1/10/2014 5/30/2014
Dept C UK002 Jane 6/6/2014 3/1/2015
Dept A UK002 Jane 3/20/2015 4/30/2015
Dept D UK003 Ria 9/2/2014 1/5/2015
Dept C UK003 Ria 1/10/2015 6/30/2015
Dept B UK004 Cindy 1/1/2015 1/31/2015
Dept A UK004 Cindy 2/10/2015 3/14/2015
Dept D UK004 Cindy 3/15/2015 4/1/2015
Dept A UK004 Cindy 4/2/2015 5/30/2015
Dept C UK005 Albert 12/12/2014 12/30/2015
I am trying to create two calculated columns. First one to get the first assignment date of an employee, second one for first assigned department.
To get the first assignment date I used the below
=CALCULATE(FIRSTDATE(Allocation[STARTDATE]),FILTER(ALL(Allocation),[EmpCode]=EARLIER([EmpCode])))
This seem to work and I get the desired result.
To get the department name, I am tried the below two options
1) CALCULATE(VALUES([DeptName]),FILTER(ALL(Allocation),[EmpCode]=EARLIER([EmpCode]) && Allocation[STARTDATE] =[first assignment date]))))
2) LOOKUPVALUE([DeptName],[STARTDATE],[First Assignment date])
Both doesn't work and I am getting #ERROR. What am I doing wrong? How can i correct this?
Desired Result should be:
DeptName EmpCode EmpName StartDate EndDate First Assignment Date First Assigned Department
Dept A UK001 John 1/1/2013 3/30/2013 1/1/2013 Dept A
Dept B UK001 John 4/3/2013 5/4/2013 1/1/2013 Dept A
Dept A UK001 John 5/5/2014 11/15/2014 1/1/2013 Dept A
Dept C UK001 John 11/19/2014 2/7/2015 1/1/2013 Dept A
Dept D UK001 John 2/15/2015 12/30/2017 1/1/2013 Dept A
Dept B UK002 Jane 1/10/2014 5/30/2014 1/10/2014 Dept B
Dept C UK002 Jane 6/6/2014 3/1/2015 1/10/2014 Dept B
Dept A UK002 Jane 3/20/2015 4/30/2015 1/10/2014 Dept B
Dept D UK003 Ria 9/2/2014 1/5/2015 9/2/2014 Dept D
Dept C UK003 Ria 1/10/2015 6/30/2015 9/2/2014 Dept D
Dept B UK004 Cindy 1/1/2015 1/31/2015 1/1/2015 Dept B
Dept A UK004 Cindy 2/10/2015 3/14/2015 1/1/2015 Dept B
Dept D UK004 Cindy 3/15/2015 4/1/2015 1/1/2015 Dept B
Dept A UK004 Cindy 4/2/2015 5/30/2015 1/1/2015 Dept B
Dept C UK005 Albert 12/12/2014 12/30/2015 12/12/2014 Dept C
Thanks for your time!
Regards,
Raghu
I am new to PowerPivot and learning by hit and miss.
I have the following table.
DeptName EmpCode EmpName StartDate EndDate
Dept A UK001 John 1/1/2013 3/30/2013
Dept B UK001 John 4/3/2013 5/4/2013
Dept A UK001 John 5/5/2014 11/15/2014
Dept C UK001 John 11/19/2014 2/7/2015
Dept D UK001 John 2/15/2015 12/30/2017
Dept B UK002 Jane 1/10/2014 5/30/2014
Dept C UK002 Jane 6/6/2014 3/1/2015
Dept A UK002 Jane 3/20/2015 4/30/2015
Dept D UK003 Ria 9/2/2014 1/5/2015
Dept C UK003 Ria 1/10/2015 6/30/2015
Dept B UK004 Cindy 1/1/2015 1/31/2015
Dept A UK004 Cindy 2/10/2015 3/14/2015
Dept D UK004 Cindy 3/15/2015 4/1/2015
Dept A UK004 Cindy 4/2/2015 5/30/2015
Dept C UK005 Albert 12/12/2014 12/30/2015
I am trying to create two calculated columns. First one to get the first assignment date of an employee, second one for first assigned department.
To get the first assignment date I used the below
=CALCULATE(FIRSTDATE(Allocation[STARTDATE]),FILTER(ALL(Allocation),[EmpCode]=EARLIER([EmpCode])))
This seem to work and I get the desired result.
To get the department name, I am tried the below two options
1) CALCULATE(VALUES([DeptName]),FILTER(ALL(Allocation),[EmpCode]=EARLIER([EmpCode]) && Allocation[STARTDATE] =[first assignment date]))))
2) LOOKUPVALUE([DeptName],[STARTDATE],[First Assignment date])
Both doesn't work and I am getting #ERROR. What am I doing wrong? How can i correct this?
Desired Result should be:
DeptName EmpCode EmpName StartDate EndDate First Assignment Date First Assigned Department
Dept A UK001 John 1/1/2013 3/30/2013 1/1/2013 Dept A
Dept B UK001 John 4/3/2013 5/4/2013 1/1/2013 Dept A
Dept A UK001 John 5/5/2014 11/15/2014 1/1/2013 Dept A
Dept C UK001 John 11/19/2014 2/7/2015 1/1/2013 Dept A
Dept D UK001 John 2/15/2015 12/30/2017 1/1/2013 Dept A
Dept B UK002 Jane 1/10/2014 5/30/2014 1/10/2014 Dept B
Dept C UK002 Jane 6/6/2014 3/1/2015 1/10/2014 Dept B
Dept A UK002 Jane 3/20/2015 4/30/2015 1/10/2014 Dept B
Dept D UK003 Ria 9/2/2014 1/5/2015 9/2/2014 Dept D
Dept C UK003 Ria 1/10/2015 6/30/2015 9/2/2014 Dept D
Dept B UK004 Cindy 1/1/2015 1/31/2015 1/1/2015 Dept B
Dept A UK004 Cindy 2/10/2015 3/14/2015 1/1/2015 Dept B
Dept D UK004 Cindy 3/15/2015 4/1/2015 1/1/2015 Dept B
Dept A UK004 Cindy 4/2/2015 5/30/2015 1/1/2015 Dept B
Dept C UK005 Albert 12/12/2014 12/30/2015 12/12/2014 Dept C
Thanks for your time!
Regards,
Raghu