Case: Model with multiple historical tables

[DataventureNL]

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.

Referencedate	Team	#Claims	Total Amount		Team deduction method
21-feb-2019	PS BB	1	1213,21		a.       Employee 2104 on Referencedate 21-feb-2019
Referencedate	Team	#Claims	Total Amount
28-feb-2019	MZL 1	1	1007,13		b.       Employee 0411 on Closuredate 26-feb-2019
Referencedate	Team	#Claims	Total Amount
31-mar-2019	MZL 2	1	1007,13		a.       Employee 0411 on Referencedate 31-mar-2019
Referencedate	Team	#Claims	Total Amount
30-apr-2019	MZL 3	1	1617,82		a.       Employee 0411 on Referencedate 30-apr-2019
Referencedate	Team	#Claims	Total Amount
31-mar-2020	MZL 3	1	1155,15		b.       Employee 3564 on Closuredate 3-mar-2020
Referencedate	Team	#Claims	Total Amount
13-dec-2020	MZL 3	1	1275,91		a.       Employee 3564 on Referencedate 13-dec-2020
Referencedate	Team	#Claims	Total Amount
31-dec-2020	MZL 3	1	1275,91		b.       Employee 3564 on Closuredate 16-dec-2020

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)

Claimnumber	ClValidFrom	ClValidTo	Status	Closuredate	Employee
4459	19-feb-2019	26-feb-2019	Open		2104
4459	26-feb-2019	28-mar-2019	Closed	26-feb-2019	0411
4459	28-mar-2019	31-dec-2019	Open		0411
4459	31-dec-2019	3-mar-2020	Open		3564
4459	3-mar-2020	11-dec-2020	Closed	03-mar-2020	3564
4459	11-dec-2020	16-dec-2020	Open		3564
4459	16-dec-2020	31-dec-9999	Closed	16-dec-2020	3564

Table 2: Finance (approx. 20M rows)

Claimnumber	Changedate	Amount
4459	19-feb-2019	1213,21
4459	24-feb-2019	-206,08
4459	17-apr-2019	610,69
4459	3-mar-2020	2256,36
4459	13-mar-2020	-2719,03
4459	16-dec-2020	120,76

Table 3: DateTable and DateTableLM (7 years history)

Date		Date	DateLM
1-jan-2015		31-jan-2015	31-jan-2015
2-jan-2015		28-feb-2015	28-feb-2015
….		..	..
18-feb-2022		31-jan-2022	31-jan-2022

Table 4: Employee (Approx. 5K rows)

Employee	EmpValidFrom	EmpValidTo	Team
2104	1-jan-1111	1-mar-2018	MZL 1
2104	1-mar-2018	31-dec-9999	PS BB
0411	1-jan-1111	28-feb-2019	MZL 1
0411	28-feb-2019	1-apr-2019	MZL 2
0411	1-apr-2019	1-jan-2020	MZL 3
0411	1-jan-2020	31-dec-9999	SR
3564	1-jan-1111	23-dec-2020	MZL 3
3564	23-dec-2020	31-dec-9999	MZL 2

[lbendlin]

Think about which tables are fact tables and which tables are dimension tables

Create a star/snowflake schema accordingly.  It is ok for one dimension table to control two fact tables. Fact tables should not be linked to each other.

For multiple dates in your logic you can create inactive relationships from the calendar dimension table to the fact tables. The your measure formula can work with USERELATIONSHIP as needed.