Hi experts,
I have a fact table with SCD2 type that contains status of customer (let's say active, pending and inactive).
I want to create a measure that shows me number of active customers in particular day (ie 2015-04-30).
The table looks more or less like this:
| ID | Time from | Time to | status |
|---|
| 1 | 2015-01-01 | 2015-03-01 | initial |
| 2 | 2015-03-02 | 2015-04-01 | pending |
| 3 | 2015-04-01 | 2015-04-30 | active |
| 4 | 2015-05-01 | 2099-01-01 | closed |
Normally in SQL it would look like:
select * from fct_customer
join dim_date
on d.gid between valid.from and valid.to
where d.gid=20150429
(sorry for possible mistakes but I hope it is more or less clear)
and in this case it would show "active"
Is there any way to do it in bi admin tool?