Hello,
I am new to PowerPivot and learning by hit and miss.
I have the following table.
To get the first assignment date I used the below
This seem to work and I get the desired result.
To get the department name, I am tried the below two options
Both doesn't work and I am getting #ERROR. What am I doing wrong? How can i correct this?
Desired Result should be:
Thanks for your time!
Regards,
Raghu
PS: I couldnt figure out how to upload the sample file.
I am new to PowerPivot and learning by hit and miss.
I have the following table.
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.
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
<tbody>
</tbody>
To get the first assignment date I used the below
Code:
CALCULATE(FIRSTDATE(Allocation[STARTDATE]),FILTER(ALL(Allocation),[EmpCode]=EARLIER([EmpCode])))
To get the department name, I am tried the below two options
Code:
[COLOR=#000000][FONT=Verdana]CALCULATE(VALUES([DeptName]),FILTER(ALL(Allocation),[EmpCode]=EARLIER([EmpCode]) && Allocation[STARTDATE] =[first assignment date]))))[/FONT][/COLOR]
OR
[COLOR=#000000][FONT=Verdana]LOOKUPVALUE([DeptName],[STARTDATE],[First Assignment date])[/FONT][/COLOR]
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
<tbody>
</tbody>
Thanks for your time!
Regards,
Raghu
PS: I couldnt figure out how to upload the sample file.