Jan 17, 2023
SCD Type 2 Fact tables are rarely used and often are not talked about. Lately, I was introduced to the concept and I was able to decipher this mystery.
In the case business logic is to give a report to the business at the end of the month based on a snap shot of how the data was at the end of month. Business needs to be able to rerun the report and get the same result every time for the same date.
Base Measure Comparison:
Regular Fact Table:
BaseMeasure =
SUM ( ‘TableName'[Amount] )
The Base Measure becomes a little complicated in SCD Type 2 Fact:
BaseMeasure =
VAR LastSelectedDate =
MAX ( ‘Date'[Date] )
RETURN
CALCULATE (
SUM ( ‘TableName'[Amount] ),
LastSelectedDate >= ‘TableName'[RowEffectiveDate],
LastSelectedDate <= ‘TableName'[RowExpirationDate]
)
Code above is basically looking for a snapshot of the data at the end of the month or selected date in your slicer, then you can do your calculation such sum of the Amount in that snapshot.Stay tuned for future blogs to see how to use role playing dimension in an SCD Type2 Fact table.