Linked Table info used to calculate a column

[miguel.escobar]

Hi!

I have a fact sales table with sales amount, date and salesman (just to name a few columns) coming from the datasource1 and I also have a linked table that has information regarding the current supervisor for the salesman with the following columns:
Salesman_Id
Supervisor_Name
Start_Date
End_Date

so basically I'd like to add a new column on the fact table that will give me the "Supervisor_Name" that should go with that salesman record for that specific date. Should I try the many-to-many approach? any suggestions?

Thanks in advance for reading this!

 

[miguel.escobar]

Any help with this would be much appreciated!
it's a pretty hardcore situation to deal with, specially because of the timeframe thing.

 

[AlbertoFerrari]

Miguel,

Take a look at this old post of mine: Alberto Ferrari : Banding with PowerPivot

The last version of the formula is the one that will help you on this: simply use CALCULATE (VALUES (...), FILTER (...XX..)) where in XX you put a suitable filter that retrieves the supervisor at the moment of the sale.
That should do the trick.

Alberto

 

[miguel.escobar]

Grazie, Alberto!

That post was more than helpful. If anyone is interested in taking a look at the workbook with the case follow the link below:
https://dl.dropbox.com/u/54063091/Relations.xlsx