I'm trying to create a Customer Count measure. Each customer has a open date and a close date in the Customer dimension, so I need to filter the count based on this date range. For each time period the count should include customers where Open Date is <= the current time period and CloseDate >= the current time period. The measure needs to work for any time granularity (years, months, etc.).
I think this query is close but it is returning a 0 count for each month:
With Member [Measures].[Customer Count] As
Count
(
Exists
(
[Customers].[Customer ID].Members,
Filter
(
[Customers].[Customer ID].[Customer ID].Members,
[Customers].[Customer Open Date].CurrentMember.MemberValue <= [Date].[Full Date].CurrentMember.MemberValue
AND
[Customers].[Customer Close Date].CurrentMember.MemberValue >= [Date].[Full Date].CurrentMember.MemberValue
)
)
)
Select
[Measures].[Customer Count] On Columns,
[Date].[Calendar Year Hierarchy].[Month] On Rows
From [Insight DW]
Where [Date].[Calendar Year].&[2006]
In this example [Date].[Full Date] is the raw DateTime value matching the key of the Date dimension.
If I replace the the two date compares in the second argument to Filter with "True" then the query returns the total row count of the dimension table. I'm assuming by this that I'm very close but just need to get my date compares right.
One problem I see is that since Full Date will always be a specific date I am probably comparing it wrong to [Customer Open Date].Current Member and [Customer Close Date].CurrentMember being that my Date granularity is months in this example (I'm selecting months on the rows of the query.)
How do I build a query like this and still keep if flexible so that it work whatever the date granularity is
Any help on this one would be VERY appreciated.
Thank you,