• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PowerPivot - Calculated Column within the table

Raghuraj

New Member
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
 

Attachments

  • SampleData.xlsx
    9.4 KB · Views: 3
Hi,

Got input at another board and this is solved now.

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

Thanks!

Regards,
Raghu
 
Hi,

First, ensure you have a power pivot table using a master table to define your summary row entries.

Then, in the fields view, navigate to the name of the master table and right click it - and select "Add Measure" Write a DAX formula to pull the data from all of your related tables.

Creating the new measure in the summary table is the key - I was trying to accomplish the same thing...trying to put the calculations in the data tables - and it wasn't working.

I'm using Excel 2016.

Thanks
 
Back
Top