Joining tables with date-slotting (Table A date is between Table B Start-End dates)

[carly]

Hey All,

Here's the scenario- I have a fact table - Table A: Employee #, Date, Metric; and a hierarchy table - Table B: Employee #, Manager #, Start Date, End Date. I want to combine these so I can retrieve the manager of an employee at the time of Table A.Date so that when I aggregate results by Employee # and Manager # the results flow to the appropriate manager.

In SAS it would look something like this: where A.Employee # = b.Employee # and a.Date between (b.Start and b.End) - but I'm brand new to PowerBI and entirely stumped here!

Thanks in advance for any help!

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.