User Profile
DataventureNL
New Member
Joined 4 years ago
User Widgets
Contributions
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: 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. 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. Employee table, this a dimension table with attributes like Team and Department. Employee(number) is the key to connect with Claim table. 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: 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. 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
