I need to calculate a year-to-date calculation for a distinct count measure.
Basically it is the number of distinct customers visited in a given time
period.

I am aware that the distinct count measures do not aggregate in AS,
so I have created a new measure as follows:

Count(
NonEmptyCrossJoin(
{[Measures].[Visits]},
Descendants(
[Customers].CurrentMember,,LEAVES)))

The [Cutsomers] dimension is a parent-child dimension.
The [Measures].[Visits] is the total number of visits, i.e. with
a sum aggregate function.

This gives me the correct values for a given period in my [Time]
dimension. My base data is at the daily level.

I would like to create an expression to generate the distinct number
of customers for year-to-date of the selected [Time].

Can anyone give me some tips on how best to construct the expression?

Regards,
Mario

try this:
Count(
NonEmptyCrossJoin(
YTD(),
{[Measures].[Visits]},
Descendants(
[Customers].CurrentMember,,LEAVES)))

or
Count(
NonEmptyCrossJoin(
YTD(MyTimeDimension.currentmember),
{[Measures].[Visits]},
Descendants(
[Customers].CurrentMember,,LEAVES)))


"MT" <MT (AT) discussions (DOT) microsoft.com> wrote

Here's a small supplement to what Jéjé suggested - the YTD() set should
only be used to filter the customer set, not actually cross-joined to
it. Otherwise a customer member could get counted for each time period
visited in YTD():

NonEmptyCrossJoin(
Descendants(
[Customers].CurrentMember,,LEAVES),
YTD(),
{[Measures].[Visits]}, 1))

or
Count(
NonEmptyCrossJoin(
Descendants(
[Customers].CurrentMember,,LEAVES),
YTD(MyTimeDimension.currentmember),
{[Measures].[Visits]}, 1))

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

oups
you are right
I've omit this point, thanks !

Just for info to MT, the "1" at the end of the nonemptycrossjoin is used to
retrieve ONLY the members of the first dimension "crossjoined". In this
case, this retrieve only the "Customers" dimensions, if you omit it, you'll
retreieve Customers + Dates which is not the same number.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Thanks. That did the job!

Regards,
Mario

"Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> wrote