Hello all,
I need to create "Smart Average" calculating sales following way:
Average of the "active months" of the customer.
For instance, if we're in November, we'll take the first month this customer bought (for instance October) and we'll create an average ([sales for October – November]/2)
How do I accomplish this in MDX?
Thanks,
Liran
Liran
You need first to find the first month when customer bought something. Unfortunately FirstNonEmpty semiadditive measure won't help here, since it will return the measure value, not the member on Time dimension itself. So you will have to do it in MDX - something like this:
Avg(NonEmpty(Date.Month.Month.MEMBERS, Measures.Sales).Item(0):Date.Month.CurrentMember, Measures.Sales)
HTH
Mosha (http://www.mosha.com/msolap)
sql