Case: Model with multiple historical tables

Goal

We want to build visuals based on multiple historical tables with time dimensions. These visuals should respond to the Referencedate (range) set by the report consumer by setting a filter or relative date slicer. The measures (amount) stem from the Finance(fact) table, dimensional columns stem from Claim table and the ‘snowflaked’ Employee table. Together with the DateTables we have the model below.

Below a short description of the tables in the model:

1. Finance table, this is a measure table with amounts, Claimnumber and Changedate. The financial situation is calculated by summing all amounts before or equal to the Referencedate.
2. Claim table, this is a dimension table with multiple attributes like Status, Closingdate and Employee. Claimnumber is the key to match with Finance table. The situation valid at the Referencedate can be determined with ClValidFrom and ClValidTo.
3. Employee table, this a dimension table with attributes like Team and Department. Employee(number) is the key to connect with Claim table.
4. Date tables, we have a basic date table that has all dates over the desired period and a connected ultimo month date table to show only the last day of each month in trend visuals. These date tables are used to populate the date filter and slicer.

We have solved time relationships in Dax measures, for example:

• TotalAmount is calculated by summing al Finance rows less/equal to Referencedate (the selected value from the Date table).
  TotalAmount =
  CALCULATE (SUM('Finance'[Amount] ),
  FILTER ('Finance','Finance'[Changedate] <= SELECTEDVALUE(DateTable[Date])))
• NumberOfClaims is calculated by counting all Claimnumbers valid at the Referencedate
  NumberOfClaims =
  CALCULATE (DISTINCTCOUNT ( 'Claim'[Claimnumber] ),
  FILTER ('Claim', 'Claim'[ClValidFrom] <= SELECTEDVALUE(DateTable[Date])

&& 'Claim'[ClValidTo] > SELECTEDVALUE (DateTable[Date])))

The difficult part is the time relationship for the Employee table: the selection of the appropriate Employee attributes (e.g. Team) depends on the status of the Claim:

1. In case of Open Claims the Employee attributes must be fetched from the row valid on the Referencedate (current Team and Department for the Employee working in the Claim). The reference date (range) is selected by the report user via date slicer/filter panel.
2. In case of Closed Claims the Employee attributes must be fetched from row valid on the Closuredate of the Claim (Team and Department of the Employee at the moment the Claim was closed), regardless of the report consumer’s Referencedate filters and slicer setting.

Below a series of table visuals with the desired output. They vary based on the Referencedate set by the report consumer in the slicer. These examples are based on the table content you see at the end of this post. The Team column is deducted from the Employee table using the time relationship logic above. Per visual you see if the open claim logic (a.) or the closed claim logic (b.) is used.

How can we achieve the time-dependent connection with the Claim table and Employee table based on the two logics described above?

I hope someone can help me out. Thanks in advance!

Table descriptions and examples

Table 1: Claims (approx. 4M rows)

Table 2: Finance (approx. 20M rows)

Table 3: DateTable and DateTableLM (7 years history)

Table 4: Employee (Approx. 5K rows)