Hello,
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!
| ID | Interval_ID | Category | start-active | end-active | FromDate | ToDate |
| 1 | 1 | A | 28.4.2010 | 2.5.2010 | 1.1.1990 | 3.5.2010 |
| 1 | 2 | A | 4.5.2010 | 4.6.2010 | 4.5.2010 | 9.5.2010 |
| 1 | 3 | B | 4.5.2010 | 4.6.2010 | 10.5.2010 | 31.1.2099 |
| 2 | 4 | A | 20.4.2010 | 15.5.2010 | 1.1.1990 | 8.5.2010 |
| 2 | 5 | B | 20.4.2010 | 15.5.2010 | 9.5.2010 | 20.5.2010 |
| 2 | 6 | B | 21.5.2010 | 1.6.2010 | 21.5.2010 | 31.1.2099 |
| 3 | 7 | B | 20.4.2010 | 3.5.2010 | 1.1.1990 | 9.5.2010 |
| 3 | 8 | B | 10.5.2010 | 25.5.2010 | 10.5.2010 | 15.5.2010 |
| 3 | 9 | A | 10.5.2010 | 25.5.2010 | 16.5.2010 | 28.5.2010 |
| 3 | 10 | A | 29.5.2010 | 4.6.2010 | 29.5.2010 | 31.1.2099 |
| 4 | 11 | A | 20.4.2010 | 5.5.2010 | 1.1.1990 | 7.5.2010 |
| 4 | 12 | A | 8.5.2010 | 25.5.2010 | 8.5.2010 | 15.5.2010 |
| 4 | 13 | B | 8.5.2010 | 25.5.2010 | 16.5.2010 | 20.5.2010 |
| 4 | 14 | B | 27.5.2010 | 5.5.2010 | 27.5.2010 | 31.1.2099 |
| 5 | 15 | B | 4.5.2010 | 4.6.2010 | 1.1.1990 | 31.1.2099 |
