This is just for sharing and discussion, the task is already solved.
You have a slowly changing dimension (SCD), e.g. sales teams with team members that change their team from time to time. Then you have facts that reference the SCD, e.g. a sales table with references to the sales team member that did the sale. Now you want to aggregate facts correctly by the SCD, e.g. total sales by sales team. Then you have to correctly consider who was when in which sales team during the period.
The SCD table has a valid from and a valid to date in each row. The fact table has a date column. Most dates in the fact table do not occur in the SCD table because they are in between change dates in the SCD table. So they cannot just be related.
- Add index column to SCD table
- Create user defined function in query editor that filters SCD table down to the correct validity period for each date from the fact table and returns the corresponding index value.
- Use this function to add index reference column to fact table.
- In data model, link the SCD index column with the facts index reference column.
Now aggregation works correctly. The basic pattern is shown in the attached file.
ReferenceFactsFromDateToDate.pbix (94.2 KB)
Feedback on alternative solutions and performance aspects is highly appreciated.