What is the best way to compute a measure on data from an SCD table?
I know how to compute a measure when a fact table is involved, but am having a trouble in extracting information from the SCD table itself.
Let's say the columns are like this:
ID
Interval_ID
Category
start-active
end-active
FromDate
ToDate
and i need to find out how many IDs were active (in the interval between start-active and end-active) in a given time period.
That is I need to pick the valid records for the period based on [FromDate], [ToDate] and then count the active IDs based on [start-active], [end-active].
Any tips on how to achieve this in the load script and/or expression would be very helpful.
See the attached qv file for my test data. Thanks!
