PowerPivot - Calculated Columns within the table

[Raghuraj]

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

<tbody>
</tbody>

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

[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.

 

[Kazlik]

I just tweaked it a bit and it worked for my with the following.

=CALCULATE(VALUES(Allocation[DeptName]),FILTER(ALL(Allocation),[EmpCode]=EARLIER([EmpCode]) && Allocation[STARTDATE] =[first assignment date]))

 

[Raghuraj]

Thanks Kazlik.

But I am getting an error "A table of multiple values was supplied where a single value was expected.

But I tried to change it and it seems to be working now.

Revised as

[COLOR=#333333]=CALCULATE([/COLOR]FIRSTNONBLANK(Allocation[DeptName],1)[COLOR=#333333],FILTER(ALL(Allocation),[EmpCode]=EARLIER([EmpCode]) && Allocation[STARTDATE] =[first assignment date]))[/COLOR]

Thanks a lot!