Finding Latest Supervisor

[Mahesh7]

Hi All,

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

1) Transactions
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]EMP ID[/TD]
[TD]EMP Name[/TD]
[TD]Sale Amount[/TD]
[TD]Sale Date[/TD]
[TD]Supervisor Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]xyz[/TD]
[TD]$1000[/TD]
[TD]12/12/18[/TD]
[TD]Sup 1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]xyz[/TD]
[TD]$100[/TD]
[TD]12/15/18[/TD]
[TD]Sup2[/TD]
[/TR]
</tbody>[/TABLE]


2) HeadCount Table

[TABLE="width: 500"]
<tbody>[TR]
[TD]EMP ID[/TD]
[TD]EMP NAme[/TD]
[TD]SUp Name[/TD]
[TD]SUP Change Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]xyz[/TD]
[TD]12/11/18[/TD]
[TD]SUp 1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]xyz[/TD]
[TD]12/14/18[/TD]
[TD]Sup 2[/TD]
[/TR]
</tbody>[/TABLE]


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.