BPC Member Formula for Prior Year Actuals?

[former_member398220]

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?

[former_member186338]

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...

[former_member398220]

Thank you, Vadim. Is there a reference to all of the available functions that are supported in BPC member formulas?

Also I have tried the following formula. It does not cause errors but no data is returned.

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

As a note, I understand that doing this in an EPM report is simple. However, we would still like to understand if this member formula could work.

[former_member186338]

What do you have in PRIOR_YEAR?

for strtomember you need full member id like:

https://docs.microsoft.com/en-us/sql/mdx/strtomember-mdx

[former_member398220]

I have updated the values to look like: [TIME].[2017.01]. Still no errors, but no results are shown in the test report.

The test report has all base level time members in the rows and the columns have ACTUAL, ACTUAL_PY in the columns.
Values are shown in the ACTUAL column but not in the ACTUAL_PY (member formula) column.

[former_member186338]

Then strtomember is not correctly implemented 🙂 Also you have to test:

[TIME].[PARENTH1].[2017.01] - full member syntax...

Anyway, use Excel formulas inside EPMOlapMemberO function to calculate a previous year period!

It will be faster then member formula.

Please read my blog: https://blogs.sap.com/2013/03/01/static-report-in-bpc-nw-10/

[former_member398220]

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