Robert you're probably looking for something like this:
IIF([StartDate]=<[Date] AND [Date]=<[EndDate], COUNT([Active], NULL)
In this case, there are only three fields, ID, Start Date, and End Date. So 'Date' and 'Active' do not exist.
I have a similar problem. But I am half way complete.
I am able to do a count using the following methodology:
1. You want to know number of Active IDs in some particular time period. Say the time period is between Ref_StartDate, Ref_EndDate. Add these two items as "Parameters"
2. Create a new Filed "Count" with the equation: IF (([Start Date]>=[Ref_StartDate] and [Star tDate]<=[Ref_EndDate]) or ([EndDate]>=[Ref_StartDate] and [EndDate]<=[Ref EndDate])) then 1 else 0 end
3. In this way I was able to calculate the count between any time period for each ID.
4. But the issue is I am able to trend the data as Count (Y-Axix bar) and ID on X-axis. But if want to add time on X-axix and do time slicing (Year, Month, Date, Day, Hour, Min), I do not have any dummy dimension which generates time sliced data between Ref_StartDate, Ref_EndDate.
Can anybody generate dummy dimension between Ref_StartDate, Ref_EndDate with 1minute as the magnitude that I am interested in.
Did you find the answer to your problem? I am facing the same issue.
I tried your solution and it did not work.
I have same issue with hours. Did you find the answer?