BPC Custom Measure issue

[former_member479324]

Hello experts,

We have created five custom measures in our SAP BPC environment that - HYTD_AVG (Half year to date Average), QTD_AVG (Quater to date Average), YTD_AVG (Year to date Average), ROY (Rest of the Year )and ROY_AVG (Rest of the year Average). The issue came in because of our fiscal year change from October to September to fiscal year: January to December. The other measures are working correctly as defined in the measure formula but the specified above are not working. Below are formulas that are not calculating correct after the fiscal year change:

HYTD_AVG MEMBER [MEASURES].[HYTD_AVG] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",AVG(PERIODSTODATE([%TIME%].[LEVEL01], [%TIME%].CURRENTMEMBER),-COALESCEEMPTY([MEASURES].[/CPMB/SDATA])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",AVG(PERIODSTODATE([%TIME%].[LEVEL01], [%TIME%].CURRENTMEMBER),COALESCEEMPTY([MEASURES].[/CPMB/SDATA])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

QTD_AVG MEMBER [MEASURES].[QTD_AVG] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",AVG(PERIODSTODATE([%TIME%].[LEVEL02], [%TIME%].CURRENTMEMBER),-COALESCEEMPTY([MEASURES].[/CPMB/SDATA])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",AVG(PERIODSTODATE([%TIME%].[LEVEL02], [%TIME%].CURRENTMEMBER),COALESCEEMPTY([MEASURES].[/CPMB/SDATA])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

ROY MEMBER [MEASURES].[ROY] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",-([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))-SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="1",([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02],ANCESTOR([%TIME%].CURRENTMEMBER, [%TIME%].[LEVEL00]))),-[MEASURES].[/CPMB/SDATA])))))';SOLVE_ORDER=3 ROY_AVG MEMBER [MEASURES].[ROY_AVG] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="1",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/12, IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="2",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/11,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="3",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/10,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="4",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/9,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="5",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/8,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="6",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/7,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="7",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/6,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="8",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/5,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="9",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/4,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="10",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/3,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="11",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/2,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="12",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/1,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="1",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00])))/12,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="2",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/11,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="3",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/10,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="4",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/9,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="5",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/8,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="6",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/7,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="7",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/6,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="8",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/5,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="9",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/4,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="10",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/3,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="11",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/2,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/XTPHXFG")="12",(+([MEASURES].[/CPMB/SDATA],ANCESTOR([%TIME%].CURRENTMEMBER,[%TIME%].[LEVEL00]))+SUM(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].PREVMEMBER),-[MEASURES].[/CPMB/SDATA]))/1,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),-[MEASURES].[/CPMB/SDATA]))))))))))))))))))))))))))';SOLVE_ORDER=3

YTD_AVG MEMBER [MEASURES].[YTD_AVG] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",AVG(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].CURRENTMEMBER),-COALESCEEMPTY([MEASURES].[/CPMB/SDATA])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",AVG(PERIODSTODATE([%TIME%].[LEVEL00], [%TIME%].CURRENTMEMBER),COALESCEEMPTY([MEASURES].[/CPMB/SDATA])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

See attached our time dimension.

Someone please suggest me on this issue. Thank you !

Regards,

Themba

[former_member186338]

Sorry, but try to explain once again!

What do you mean by "The issue came in because of our fiscal year change from October to September to fiscal year: January to December." - how it was changed, what was done??

And for sure, start with single not working measure...

P.S. What is the cube storage type?

[former_member479324]

Hi,

As requested, see below the screen shot

Please advise of the possible solution.

Thanks,

Themba

[former_member186338]

Nothing wrong with hierarchy.

Sorry, but I don't have BPC 7.5 to test!

Try to test something simple yourself to find the issue...

[former_member479324]

Hi,

We still on BPC 7.5, we not BPC 10.1 yet.

Please advise on the possible solution.

Thanks,

Themba

[former_member186338]

When you are selecting member in Excel report you can see hierarchy... Try to make a screenshot

[former_member479324]

Hi Vadim,

We change the time dimension property: MONTHNUM to reflect the new fiscal year of the company, we change the Quaters to also reflect the new fiscal year. This was done the time dimension. The cube storage type is PER(Periodic).

Your help in this regard would be appreciated.

Thanks,

Themba

[former_member186338]

I see other correct changes:

BASE_PERIOD: 2016.JAN 001

PERIOD: 2016.JAN JAN

etc...

Can you show screenshot of your hierarchy view of TIME dimension in WEB admin?