Date & Cumulative sum related MDX issue RRS feed

  • Question

  • I have a scorecard that is displaying actuals, forecasts and projections for the months in the chosen year.
    I would like to display Actual till the last month and the forecast from the current month onwards. Projection needs to be a cumulative total of Actual till the last month and forecast from the current month. 

    So it looks something like this... I am displaying months from the beginning of the year and a few from the end of the year. I have cumulative totals working for Actuals;                            Actual  Forecast Projection
    Jan 2009               100                   100
    Feb 2009               100                   200
    Oct 2009                           100       1000
    Nov 2009                            50        1050
    Dec 2009                            50        1100

    My 2 problems are:

    1. I want to display actuals <= last month and forecasts >= current month. My date filter is at the year level and I have tried a bunch of things with no luck...
    Here is my code specified in the KPI: IIF( Descendants([Date].[Calendar].CurrentMember, [Date].[Calendar].[Month]).Item(
      Descendants([Date].[Calendar].CurrentMember, [Date].[Calendar].[Month]).Count -4) <= [Last Month].Item(0) ,  ([Production Amount], [Commissionable Premium]), Null)

    As you can see I have hardcoded the -4 and it still doesn't work...

    2. I do not know how to use cumulative totals for one or the other measure. Projection is actuals till last month and forecast from current month
    This code works but only for the 1 measure not incorporating both....
    PeriodsToDate([Date].[Calendar].[Year],[Date].[Calendar].CurrentMember) , ([Prod Amount] ) )

    Thanks in advance for any help in solving these issues.
    Friday, October 23, 2009 10:36 PM

All replies

  • This is really not a PPS M&A specific related issue, would probably be better if you posted this in the SSAS forums - http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/threads.

    If you are viewing the information by Month/Year I was kind of curious as to why you were doing descendants to the Month level since you would already be at the Month level when viewing the data.

    As far as the second one don't you just need to add in the Prod Forecast Amount for the CurrentMember into your MDX statement?

    Anyway, I would still move this to the SSAS forums.
    Dan English's BI Blog
    Please mark posts as answer or helpful when they are.
    Saturday, October 24, 2009 1:07 PM
  • Thanks for your response. I have moved it to the SSAS forum.

    Monday, October 26, 2009 7:34 PM