CODEWITHSUNDEEP
ssasmdx
DistinctUser
DistinctUser

Reputation: 1

SSAS MDX sum up on memberships in date hierarchies?

In a cube that contains memberships of a club, I have a column MembersInOut in my fact-table which holds when a member joined the club (Value = 1) and leaving (value = -1). The Club started jan 1. 2000. so no members before that date.

Now to know the current number of members on a specific date I can do this:

CREATE MEMBER CURRENTCUBE.[Measures].[Calculated MembersOfTheClub]
AS

     Sum(
            {[Date Dim].[Date].&[2000-01-01T00:00:00]: 
             [Date Dim].[Date].currentmember},
             [Measures].[MembersInOut] 
            )

This works fine on the actuel date, but how to make this work on a date hierarchie [Year-Month-day] ?

Thanks

Upvotes: 0

Views: 177

Answers (1)

zoe zhi
zoe zhi

Reputation: 164

You could create Y-M-D hierarchy, then use expression like below 

with member[Measures].[S1] AS
sum(
      {NULL:[Date].[Calendar Date].CurrentMember}  
    , [Measures].[Internet Sales Count])

select nonempty ([Date].[Calendar Date].members) on rows, nonempty ({[Measures].[S1],[Measures].[Internet Sales Count]}) on columns from [Analysis Services Tutorial]

enter image description here

Zoe

Upvotes: 1

Related Questions