March 18, 2015 at 8:45 am
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
March 18, 2015 at 11:09 am
A few questions to see if everything is in place:
Hope this helps.
March 19, 2015 at 7:03 am
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?
March 19, 2015 at 9:17 am
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.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy