locked
Forcast the remaining days of the month RRS feed

  • Question

  • I've been scanning posts on creating a Forecast measure but haven't seem to find what I'm looking for.

    I have a simple cube with measures for Actual, Goal, Potential, with a measure dimension with 3 measures of Volume, Revenue, Margin, and a Calendar Date dimension with a Hierarchy for Year, Quarter, Month, Day

    The calculation I want to perform is IF the calendar day is < today then 0 else (MTD (which I have) / today's day) * currentmember day.

    I have started slowly with my MDX, but I can't seem to get the IIF statement to compare correctly.

    WITH Member Measures.Forecast AS

    IIF ([Calendar Date].[Calendar Date].properties("Calendar Day Of Month") < 20,0,[Measures].[MTDActual])

    Select Measrues.Forecast on Columns,

    ([Calendar Date].[Calendar Date Hierarchy].[Calendar Month].&[2012]&[1]&[2].childresn) on rows

    From Cube

    Any help would be great.


    JB

    Tuesday, February 21, 2012 2:01 PM

Answers

  • Try this

    WITH Member Measures.Forecast AS

    IIF ([Calendar Date].[Calendar Date].CurrentMember.properties("Calendar Day Of Month", TYPED) < 20,0,[Measures].[MTDActual])

    Select Measrues.Forecast on Columns,

    ([Calendar Date].[Calendar Date Hierarchy].[Calendar Month].&[2012]&[1]&[2].childresn) on rows

    From Cube


    -Remember to mark as helpful/the answer if you agree with the post.

    • Marked as answer by Challen Fu Tuesday, March 6, 2012 4:55 AM
    Tuesday, February 21, 2012 9:20 PM