Dynamic Row Level Security in a historical environment

There have been a number of questions about Dynamic Row Level Secutiry and I just could not figure out how to make it work in my environment.

Apologies this project is too big to replicate:

Here is the structure:

Active Employee table

Data_as_of (last day of each month for 7 years)

Employee ID

Division

DivisionCode (Date and Division combined)

Headcount (generally always a 1)

Status (Active, Contractor, leave of Absense, etc)

Email

Terminations table

Data_as_of (last day of each month for 7 years)

Employee ID

Division

DivisionCode (Date and Division combined)

Termination Date

Hires table

Data_as_of (last day of each month for 7 years)

Requisition ID

Position ID

Start Date

Division

DivisionCode (Date and Division combined)

Organizational table

Data_as_of (last day of each month for 7 years)

Division

DivisionCode (Date and Division combined)

Company

Division name

DivisionCode column is generally linked to every table

Standard Date Dimension table

Date column is generally linked to Data_as_of in each table

I have four companies

Each company has 6-22 Divisions

I have a CEO of all companies

I have a Company Lead for each company

I have a Division Head for each division

I have two report pages that includes YTD (end of month) and Quarterly numbers:

1. I have an all Companies page that shows standard HR metrics (headcount, turnover, hires, etc) across all four companies.
2. I have a My Group page that displays the same visuals only by Division instead of by Company. (usually in the form of a Legend)

IMPORTANT: everyone sees everything in the All Companies page.

• When the CEO looks at the My Group I want them to see everything
• When the Company Lead looks at the Dashboard I want them to see just their company and all its Divisions
• When a Division Head logs in I want them to see just their Division.
  What they are is largely manual - there is no field that captures this.

The complication comes in because we're displaying 4 years worth of data. And positions have changed but I want everyone to be able to see all the historical metrics. For instance our Divisional Head of HR is new to the position but I want them to be able to see the headcount of his Division back to 2016. AND I want to filter the My Company page but not the All Companies page.

I get that there is some manual effort and I will likely have to make a Managers Table, but do I have to do one for every month for the past four years of the entire structure?

Please help me create a model and roles that make sense.

Thank you all so much for your time and effort.

Best regards,

~Don