Finding Latest Supervisor

[Mahesh7]

Hi All,

I have a quick question regarding PowerPivot calculate column. I have below tables

1) Transactions

EMP ID	EMP Name	Sale Amount	Sale Date	Supervisor Name
1	xyz	$1000	12/12/18	Sup 1
1	xyz	$100	12/15/18	Sup2

<tbody>
</tbody>

2) HeadCount Table

EMP ID	EMP NAme	SUp Name	SUP Change Date
1	xyz	12/11/18	SUp 1
2	xyz	12/14/18	Sup 2

<tbody>
</tbody>

I want update the supervisor Name column in Transacitons table basis HeadCount Table. That is Sup name should reflect as per supervisor change date in HeadCount Table.

eg. For all the transactions from 12/11/18 to 12/13/18 sup name should be SUp 1 and Sup2 after that.

I am using powerpivot. I have done this in normal excel table using lookup formulas but its not working in PoserPivot.

Regards,
Mahesh

 

[Hashiru]

Hi Mahesh,

Your formula in Excel might give better understanding of your objective.