Custom measure BPC 10 NW

[Former Member]

Hi All,

I am implemented an YearToGO custom measure, I need to set a value equal to last month of an year (e.g. year 2017 I need 2017 december)

I am trying to use TAIL function but when I add the custom measure in the report the data area became blank.

I have tested the code on T-code MDXTEST and it is working fine.

Following the code (only the part with tail function):

IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",

TAIL([%TIME%].[LEVEL01],1)

Thanks

Nilla

[former_member186338 · Accepted solution]

Try this one:

'([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00])))';SOLVE_ORDER=3

[Former Member]

Hi Vadim,

Thanks a lot! it works fine!

Just another question, I have to create a new formula every time that I insert a change in the code. It seems that it is not possible to change the code after the first save.

Do you know if there is a way to avoid this?

[former_member186338]

Please Accept the correct answer and close the question!

801 SP09 - very old! Upgrade required...

EPM SP20 is also old!

[former_member186338]

Please Accept the correct answer and close the question!

BPC 801 SP09 is very old, upgrade required!

EPM SP20 is also old!

To edit measure use:

[Former Member]

Thanks, I used the code but the result is not what expected.

[former_member186338]

Try the following for AST:

'([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME_PL%].[LEVEL00]))';SOLVE_ORDER=3

[Former Member]

yes sorry you are write, I put

TAIL([%TIME_PL%].[LEVEL01],1)

it was a wrong copy and paste.

but with this instruction when I add the custom formula the report became empty.

[former_member186338]

But what do you mean by:

TAIL([/CPMB/MOD19WI].[LEVEL01],1)

Has to be:

TAIL([%TIME_PL%].[LEVEL01],1)

[Former Member]

ok sorry, please find below the full code

IIF([%FUNCT_ACCOUNT_PL%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC", SUM(PERIODSTODATE([%TIME_PL%].[LEVEL00], CLOSINGPERIOD([%TIME_PL%].[LEVEL00])),-[MEASURES].[/CPMB/SDATA])-SUM(PERIODSTODATE([%TIME_PL%].[LEVEL00], [%TIME_PL%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]), IIF([%FUNCT_ACCOUNT_PL%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP", SUM(PERIODSTODATE([%TIME_PL%].[LEVEL00], CLOSINGPERIOD([%TIME_PL%].[LEVEL00])),[MEASURES].[/CPMB/SDATA])-SUM(PERIODSTODATE([%TIME_PL%].[LEVEL00], [%TIME_PL%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA]), IIF([%FUNCT_ACCOUNT_PL%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",

TAIL([/CPMB/MOD19WI].[LEVEL01],1),

IIF([%FUNCT_ACCOUNT_PL%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ", NULL, -[MEASURES].[/CPMB/SDATA])))) ';SOLVE_ORDER=3

Following the expected result.

The formula for EXP is fine

But For AST we need to have the last value of the year. The AST account is used to store the FTE.

[former_member186338]

Please explain what do YOU mean by YearToGO measure with some example screenshot!

[former_member186338]

P.S. Also it's a strange idea to show only part of custom measure...