Search code examples
dateexcel-2010reportingmdxssas-2008

YTD function not working on Month level (SSAS 2008)

I've created a YTD calculated member in an SSAS cube using the ytd()function. This function looks like this:

SUM(
  YTD(
   [Date].[Calendar].[Calendar Year]
  ,[Date].[Calendar]
  )
 ,[Measures].[cost]
)

Everything is working fine when in my pivotTable I use the calendar hierarchy in rows(excel 2010). But When I try to use my YTD with the Month level only, I have a value which is the total cost of the year. There is no aggregation by Month. I have something like this: 

      MONTH  |  YTD;
     01       ,   240;
     02       ,   240;
     03       ,   240;
     04       ,   240;
     05       ,   240;
     ...      ,   240;

When I drag the quarter above the month in my report, then I have the expected values. The month is the only level that raises that issue and I don't understand? My date dimension is well declared as time dimension and each attribute set correclty.

Solution

  • I got it! Indeed my time hierarchy wasn't correct. In fact in my Time Attribute Relationships, my YEAR, QUARTER, MONTH attributes were related to the only DATE attribute (set as the primary Key). I just removed the relationship between the Date and QUARTER attribute and I recreated a new one between the MONTH and the QUARTER. Now my YTD measure is aggregated correctly within months! Problem solved:-)