Home Forums Data Warehousing Analysis Services MDX for SUM(YTD) returns Nulls RE: MDX for SUM(YTD) returns Nulls

  • 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.