none
calculation with first day of the year. RRS feed

  • Question

  • I'm having a measures defined on a yearly level (connected to the 1st january of each year).

    I'm having a calculation which defines a result (ratio) for each day (lowest grain)

    Now I want to multiply this ratio with that yearly measure but make it applicable for my entire period dimension.

    When I "hardcode" the formula like "[Period].[date].&[20130101],[Measures].[ForeCast]" it works.

    But ofcourse I want to make this "[Period].[date].&[20130101]" dynamic based on the selected member of the dimension period.

    Is there a way to do this?

    Thanks

    Monday, January 28, 2013 9:29 AM

All replies

  • Nobody had to challange this before?
    Wednesday, January 30, 2013 10:42 AM
  • Do you mean you want a parameterized MDX? Any specific reason, not to use OPENINGPERIOD()

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Wednesday, January 30, 2013 11:05 AM
  • the real solution would be to have the measure in another measure group, and bind it to the year level, and not the day level.

    but here is the MDX you are looking for: (OpeningPeriod([Period].[date].[Year], [Period].[date].CurrentMember), [Measures].[ForeCast])

    Wednesday, January 30, 2013 11:55 AM
  • You might find that the above mdx returns the forecast for the year.

    There are many ways to write the query.  here's one, assuming day is 2 levels below year.  It goes to the year of the current period, then the first month, then the first day of the month.  If you have more levels between year and day, just add in more firstchilds.

    (ancestor([Trading Dates].[Calendar YMD],[Trading Dates].[Calendar YMD].[Calendar Year]).firstchild.firstchild, [Measures].[ForeCast])


    Richard

    • Proposed as answer by RichardLees Monday, February 4, 2013 2:01 AM
    • Marked as answer by Elvis LongModerator Monday, February 4, 2013 2:06 AM
    • Unmarked as answer by BramDe Monday, February 4, 2013 8:36 AM
    • Unproposed as answer by BramDe Monday, February 4, 2013 8:40 AM
    Friday, February 1, 2013 3:54 AM
  • The point is to make it work on every member of the dimPeriod, including in the hierarchy. Eventually I came it out on this:

    (Measurs.Ratio * head(NonEmpty([Period].[Year].CurrentMember * [Period].[Date].Members * Measurs.[x]),1).Item(0))

    It would be nice if you give me some query tuning options but this works for every selected member.

    Thanks,

    Bram

    Monday, February 4, 2013 8:39 AM
  • For some Reason OpeningPeriod returns me the null value ...
    Monday, February 4, 2013 8:50 AM