MDX for SUM(YTD) returns Nulls

• sroberts 84045

  SSC Veteran

  Points: 288

  More actions

  March 18, 2015 at 8:45 am

  #145666

  Hello,

  I am trying to write what should be a simple MDX script as a CALCULATED MEMBER in SSAS 2012. I am trying to create a measure that is a YTD Sum of Current Month Actual.

  My Time Dimension is "FISCAL PERIOD" and is established as follows:

  FISCAL YEAR = Year

  FISCAL DATE = Date (This is a date field that is just the 1st of every month)

  The Key on this Dimension is FISCAL PERIOD which is in the format YYYYMM and is not assigned as a Time Field because I am not sure what to assign it to.

  The Relationship for the Dimension is FISCAL PERIOD --> FISCAL DATE --> FISCAL YEAR

  This Dimension has a relationship to the Measure via FISCAL PERIOD

  The MDX for the Calculated Measure is:

  CREATE MEMBER CURRENTCUBE.[Measures].YTD_Actual

  AS sum(YTD([FISCAL PERIOD].[Fiscal Date].CurrentMember),[Measures].[Current Month Actual]),

  VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'VW BUDGET VS ACTUAL';

  I am able to process the cube without error. However, the YTD_Actual measure returns a NULL, even if I filter on a specific FISCAL DATE.

  Anyone have any thought?

  Steven

• Martin Schoombee

  SSCoach

  Points: 19135

  More actions

  March 18, 2015 at 11:09 am

  #1784850

  A few questions to see if everything is in place:

  • Do you have the type property set to "Year" for the year attribute in your dimension. According to BOL, the YTD() function will only work if that was done.
  • Are you including a date member on your axis of your MDX query. If not, there is no "currentmember" for the date hierarchy and could be a reason for the nulls.
  • From what you've described above, it doesn't seem like you have a user hierarchy defined. Aside from the attribute relationships, the YTD() function needs a user hierarchy to be in place.

  Hope this helps.

• sroberts 84045

  SSC Veteran

  Points: 288

  More actions

  March 19, 2015 at 7:03 am

  #1785029

  Martin, thanks for your reply. If I understand your questions correctly, I believe everything I need is in place. To be sure:

  - Yes. "FISCAL_YEAR" is set to YEAR

  - The MDX query is as follows: SUM(YTD([Fiscal Period].[Fiscal Date].CurrentMember),[Measures].[VW BUDGET VS ACTUAL].[Current Month Actual]) and "FISCAL_DATE" is set to the type DATE

  - The Hierarchy for the FISCAL_YEAR dimension is:

  . FISCAL_YEAR

  .. FISCAL_DATE

  ... FISCAL_PERIOD (which is another field in the DIM "FISCAL_PERIOD", having the format YYYYMM and not set to a Type.

  Thanks

  P.S. Do I need a Month type to do YTD?

• Martin Schoombee

  SSCoach

  Points: 19135

  More actions

  March 19, 2015 at 9:17 am

  #1785097

  sroberts 84045 (3/19/2015)

  ---

  Martin, thanks for your reply. If I understand your questions correctly, I believe everything I need is in place. To be sure:

  - Yes. "FISCAL_YEAR" is set to YEAR

  - The MDX query is as follows: SUM(YTD([Fiscal Period].[Fiscal Date].CurrentMember),[Measures].[VW BUDGET VS ACTUAL].[Current Month Actual]) and "FISCAL_DATE" is set to the type DATE

  - The Hierarchy for the FISCAL_YEAR dimension is:

  . FISCAL_YEAR

  .. FISCAL_DATE

  ... FISCAL_PERIOD (which is another field in the DIM "FISCAL_PERIOD", having the format YYYYMM and not set to a Type.

  Thanks

  P.S. Do I need a Month type to do YTD?

  hmmm...not sure if you need a month hierarchy-level for the YTD() function to work.

  Have you tried running this as part of an ad-hoc query in Management Studio? If not, give that a try and see what it comes back with. I would also try the same thing but with the PeriodsToDate() function instead of YTD, to see if that works.