SSAS Calc & MDX Query -- NEWBIE

• tlenzmeier

  SSC Rookie

  Points: 37

  More actions

  September 12, 2016 at 11:36 am

  #321462

  I know folks have asked this before, but none of the answers I've seen address my particular problem. I have a cube based on the AdventureWorksDW database, reseller sales. I am trying to get the moving average and I have the following in my script:

  CALCULATE;

  CREATE MEMBER CURRENTCUBE.[Measures].[Reseller Sales Moving Average]

  AS Avg

  (

  [Date].[Calendar].CurrentMember.Lag(6):

  [Date].[Calendar].CurrentMember,

  [Measures].[Reseller Sales Amount]

  ) // This calculation returns the average value of a member over the specified time interval.

  ,

  FORMAT_STRING = Standard,

  NON_EMPTY_BEHAVIOR = { [Reseller Sales Amount] },

  VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Reseller Sales' ;

  I checked the syntax and it appears to be correct.

  Now when I run a query in SSMS, nothing comes back.

  SELECT {Measures.[Reseller Sales Amount], Measures.[Reseller Sales Moving Average]} ON 0,

  NONEMPTY([Date].[Calendar].[Month].Members) ON 1

  FROM [Reseller Sales] -- reseller sales is the name of my cube

  Grateful for any insight on my ridiculously simple problem.

• tlenzmeier

  SSC Rookie

  Points: 37

  More actions

  September 12, 2016 at 4:53 pm

  #1900433

  I figured it out.

• Koen Verbeeck

  SSC Guru

  Points: 259155

  More actions

  September 13, 2016 at 2:53 am

  #1900472

  tlenzmeier (9/12/2016)

  ---

  I figured it out.

  Would you mind posting the solution so that people with a similar problem might benefit?

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• tlenzmeier

  SSC Rookie

  Points: 37

  More actions

  September 13, 2016 at 6:58 am

  #1900526

  My mistake was to grab Date.dim when I should have grabbed Due Date from the metadata tab. So the correct syntax is:

  SELECT {Measures.[Reseller Sales Amount], Measures.[Reseller Sales Moving Average]} ON 0,

  NONEMPTY([b][u]Due Date[/u][/b].[Calendar].[Month].Members) ON 1

  FROM [Reseller Sales] -- reseller sales is the name of my cube