Skip to Content

BPC Member Formula for Prior Year Actuals?

I thought this would be a lot easier than it has turned out to be...

BPC 10.1 NW on HANA

Looking to be able to easily report on prior year actuals by selecting a Category/Version.

My initial formula was:

[VERSION].[ACTUAL], [TIME].CURRENTMEMBER.PROPERTIES("PRIOR_YEAR")

However, I got a CX_SY_CONVERSION_NO_NUMBER error and after some searching, figured that I probably can't reference another time member in a property like that.

So I've experimented with the LAG function but that seems to bomb out on quarter/year parent levels. It works fine if I only have a condition for months but when I add the additional IIF statements for Quarter/Year, I get the same error as above.


IIF([TIME].CurrentMember.Properties("LEVEL")="MONTH",([TIME].CURRENTMEMBER.LAG(12),[VERSION].[ACTUAL]),IIF([TIME].CurrentMember.Properties("LEVEL")="QUARTER",([TIME].CURRENTMEMBER.LAG(4),[VERSION].[ACTUAL]),IIF([TIME].CurrentMember.Properties("LEVEL")="YEAR",([TIME].CURRENTMEMBER.LAG(1))[VERSION].[ACTUAL]))))

Any tips?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jun 21, 2017 at 05:15 PM

    In general I do not recommend using dimension member formula to get prev year data. This can be easily achieved using EPM formulas in the report. Please show the report sample you want to create!

    P.S. your formula:

    [VERSION].[ACTUAL], [TIME].CURRENTMEMBER.PROPERTIES("PRIOR_YEAR")

    require strtomember mdx function...

    Add comment
    10|10000 characters needed characters exceeded

    • I tried it fully-qualified as you suggested but still no data.

      Thanks for the EPM tip - we already have the report written like that but would like to change over to using a member formula. member formulas are actually really fast with HANA MDX so performance isn't an issue. Functionality and user-friendliness is key here.

      So am I correct in saying that the MDX syntax doesn't work because of a software issue?

      Thanks