Skip to Content
-1
Jul 08, 2019 at 07:43 AM

BPC Custom Measure issue

190 Views

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

Attachments

time-prop.png (36.2 kB)