cancel
Showing results for 
Search instead for 
Did you mean: 

Custom measure BPC 10 NW

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

Try this one:

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

Answers (7)

Answers (7)

Former Member
0 Kudos

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
Active Contributor
0 Kudos

Please Accept the correct answer and close the question!

801 SP09 - very old! Upgrade required...

EPM SP20 is also old!

former_member186338
Active Contributor
0 Kudos

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
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

Try the following for AST:

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

Former Member
0 Kudos

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
Active Contributor
0 Kudos

But what do you mean by:

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

Has to be:

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

Former Member
0 Kudos

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
Active Contributor
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

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