MDX for SUM(YTD) returns Nulls

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19116 More actions · Answer 1

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 · Answer 2

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: 19116 More actions · Answer 3

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.