Skip to Content
avatar image
Former Member

Help with MDX Member Formula in BPC 10.1

Dear,

I have created a BPC 10.1 Standard model with the following dimensions:

- Category;
- Sales Org;
- Measure;
- Currency;
- Time.

For example, in the current Rolling Forecast volumes are stored in Measure "VOLUME", Category "RF201802". Actuals Volumes are stored in Measure "VOLUME" and Category "ACTUALS".

I have Measures like "Prior RF Volume" and "Prior Year Volume" and my idea is to use Member Formula to retrieve the Prior RF Volume for the same month and the Prior Year Actuals. I thought to use an attribute on Category to identify the Prior RF and an Offset on Time Dimension for the CURRENTMEMBER, but tried different MDX codes and not able to achieve it.

Does somebody know how can i do these two Measures using MDX?

Thanks,

Andre

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Feb 28 at 08:06 PM

    Sorry, but not clear! "I have Measures like "Prior RF Volume" and "Prior Year Volume" and my idea is to use Member Formula to retrieve the Prior RF Volume for the same month and the Prior Year Actuals." - what do you mean???

    You are talking about custom measure or member formula?

    Please spend some time to accurately explain your requirements with some data sample!

    P.S. Not all dimensions specified for the model (Account?) - better to provide screenshot!

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 28 at 10:11 PM

    Hi Vadim, thanks for your quick response.

    These are the dimensions in my model (in the first post i translated form Portuguese to English):

    I have three members in METRICA (Account) dimension, PREVISAO, PREVISAO_MES_ANTERIOR and HISTORICO_MES_ANTERIOR. In the screenshot below, I have inputs for PREVISAO for CICLO (Category) 022018:

    For the next CICLO, which is 032018, PREVISAO_MES_ANTERIOR should show the values from the Previous CICLO, which is 022018:

    For this scenario I thought to use a property in CICLO dimension to identify the Previous CICLO, and retrieve values using a Member Formula (MDX), but i couldn't achieve it using FILTER MDX command.

    The second scenario is METRICA HISTORICO_ANO_ANTERIOR, which in this case, should retrieve Actuals 2017 values from CICLO = ACTUALS, doesn't matter the selected CICLO by the user:

    For this scenario I thought to use a MDX formula to Offset the period, but couldn't find anything either.

    Please let me know if it is clear now.

    Thanks,

    Andre

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 28 at 10:27 PM

    Hi,

    I think your question is more in regard to runtime MDX query. If I am correct then the following maybe helpful to provide a perspective for your design questions.

    The following are important factors

    • BPC measure formulas work with account types and Time dimension. This is easy to see by just checking BPC standard measure. In this regard the account and time dimensions are the most important in the measure formulas.
    • Dimension formulas can be defined on any dimension and they will become part of the runtime MDX query.

    The runtime MDX query that is generated for a report is the result of report structure, member selection and so on, measure formulas and dimension formulas.

    Please let me know if you are interested in having more overall information overall about runtime MDX queries and how they are constructed.

    Best Regards,

    Leila Lappin

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 01 at 06:56 PM

    For the first case with property currently I don't know a solution (at least for my BW750 SP06 BPC standard).

    The correct formula has to be like (test in the copy of ENVIRONMENTSHELL):

    Property for CATEGORY dimension has to contain the full name of prev CATEGORY member:

    PREVCAT property: for member Plan contain: [CATEGORY].[Budget] etc...

    ([PL010],STRTOMEMBER([CATEGORY].CURRENTMEMBER.PROPERTIES("PREVCAT")))

    [CATEGORY].CURRENTMEMBER.PROPERTIES("PREVCAT") will return string "[CATEGORY].[Budget]"

    and

    STRTOMEMBER(...) will convert the string to member

    For example STRTOMEMBER("[CATEGORY].[Budget]") will return correct result

    Unfortunately combination of PROPERTIES and STRTOMEMBER is not working correctly (old bug, still not solved).

    For the second case ParallelPeriod will work fine! Correct formula:

    ([PREVISAO],PARALLELPERIOD([TIME].LEVEL00,1,[TIME].CURRENTMEMBER))

    1, not -12 ! "1" - means lag one year!

    Add comment
    10|10000 characters needed characters exceeded