Use Date Table to Filter Dimension Employee Table

Hello,

I am currently try to build a more intelligent way to view some of our reports that way it can incorporate historical data, instead of only showing current data.

I have a dimension employee table with historical records of changes (position, employment status, supervisor, etc.) which is the lens we use to analyze some of our fact tables. Currently in most of our reports we use a table that only pulls in Status = Active and CurrentRow = Y employees, which gives us one record for each active employee and is the current record.

In general, this works well, but it only allows user to look at current data. 

I would like to be able to join this table with a date table, so that you can filter using other time metrics. My hope is to build something that utilizes the employee start and end date instead of Current Row. The employee status will still be used as if an employee is terminated the status will be terminated, but the Current Row will be Y.

For example, filter the date table by 2021 and show all of the employees for that year with the other related info like position. Then you could change to 2022 and see any of the updated info.

For the example data I provided above if you filtered by 2021 you would see that Bob's position is ABC, but if you filtered by 2022 you would see he changed positions to DEF.

Any suggestions on how to accomplish this would be much appreciated.