Rolling 12 months Custom Measure for YTD model

[Former Member]

Hi,

I have one Consolidation Model(YTD Model) and one Planning model(Periodic Model).

Data is uploaded from BW to BPC Consolidation Model with 13 Dimensions.

Data is transferred from Consolidation model(13 Dimensions) to Planning Model(8 Dimensions) using script logic.

User have to input Planning data for Budget and Forecast in Planning Model.

Planning model is having Custom Measure Rolling 12 months, we used this to create rolling forecast reports.

Once planning data(Budget and Forecast) approved, the same need to transferred to Consolidation Model using script logic.

I have data in Consolidation model with Actual, Budget and Forecast with 13 dimensions.

I have used below Rolling forecast measure in Periodic model.

Now i need to create rolling forecast with similar Rolling 12 months measure( I have used Periodic Formula as basis). Can anyone give some hint whether i have to use YTD formula or Periodic formula as basis and some tips on how to code this Rolling 12 Months measure in YTD model.

Thanks.

[former_member186338 · Accepted solution]

Sorry, but what is YOUR definition of Rolling 12 months measure in YTD model?

Please show the desired result

[Former Member · Accepted solution]

Hi Vadim,

My Report requirement is:

Current month is 2015.07

2014.01 to 2014.12 (each month as column) ---> Actual

2014.12 Total ( as one column) --> Actual

2015.01 to 2015.07 (each month as column) --> Actual

2015.08 to 2015.12 (each month as column) --> Forecast

2015.12 Total ( as one column) --> 7 months Actual + 5 months Forecast ( this I copied actuals to Forecast and showing Forecast 12 month YTD)

2016.01 to 2016.07 ( each month as column) --> Forecast

2016.07 Total ( as one column) --> 2015.08 to 2016.07 - 12 months Forecast Total

2015.01 to 2015.12 (each month as column) --> Budget

2015.12 Total ( as one column) --> Budget

2016.01 to 2016.12 (each month as column) --> Budget

2016.12 Total (as one column) --> Budget

As per my understanding got the 2016.07 Total (comprise of 12 months data from 2015.08 to 2015.07)

can be achieved  using Custom Measure.

I have same report in my Planning Application, i have created R12 as my custom measure.

Now in YTD Consolidation model too i have same requirement, so u need to create Custom measure which can populate 12 months Forecast data.

Thanks.

[former_member186338 · Accepted solution]

Then you have to start with a copy of Periodic measure of YTD cube. Then use sum of last 12 months of Periodic measure:

SUM(LASTPERIODS(12,[%TIME%].CURRENTMEMBER),[MEASURES].[PERIODIC])...

[Former Member · Accepted solution]

Hi Vadim,

I have created Custom Measure as below, i am getting error as " Member invalid on Dimension(SCOPE).

Is my Code correct?

Thanks

[former_member186338 · Accepted solution]

Not correct, inside this code you have to copy code of the periodic measure.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

As suggested, i have added the code of periodic and made changes to R12 custom measure, still getting the same error as Members Invalid in Dimension(SCOPE). Here is the new code.

MEMBER [MEASURES].[YTD] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])))' SOLVE_ORDER=3

MEMBER [MEASURES].[PERIODIC] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JUL" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

[MEASURES].[R12] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP"), SUM(LASTPERIODS(12,[%TIME%].CURRENTMEMBER),[MEASURES].[PERIODIC]), [MEASURES].[PERIODIC])' SOLVE_ORDER=3

[former_member186338 · Accepted solution]

Just tested, works ok!

Measure text:

MEMBER [MEASURES].[YTD] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])))' SOLVE_ORDER=3

MEMBER [MEASURES].[PERIODIC] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

MEMBER [MEASURES].[R12] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP"), SUM(LASTPERIODS(12,[%TIME%].CURRENTMEMBER),[MEASURES].[PERIODIC]), [MEASURES].[YTD])' SOLVE_ORDER=3

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I have one problem with this custom measure.

When I use the new Custom measure in the report my report which usually take 15 seconds to bring the data, now takes 6 minutes.

Could you please let me know what needs to be done. I have recheck the same report without Custom Measure R12, run time is 15 seconds only.

Thanks,

[former_member186338 · Accepted solution]

Yes, this is an issue with complex custom measures

You may try to improve formula (rewrite):

R12=Currentmember + LAG(12).Parent.Parent - LAG(12)

For 2015.11 will be

R12= 2015.11+2014.TOTAL-2014.11

For 2015.12 will be

R12= 2015.12+2014.TOTAL-2014.12=2015.12

Vadim

[former_member186338 · Accepted solution]

Something like (not complete):

MEMBER [MEASURES].[R12] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC"),-([MEASURES].[/CPMB/SDATA],[%TIME%].CURRENTMEMBER)-([MEASURES].[/CPMB/SDATA],[%TIME%].LAG(12).PARENT.PARENT)+([MEASURES].[/CPMB/SDATA],[%TIME%].LAG(12)),([MEASURES].[/CPMB/SDATA],[%TIME%].CURRENTMEMBER)+([MEASURES].[/CPMB/SDATA],[%TIME%].LAG(12).PARENT.PARENT)-([MEASURES].[/CPMB/SDATA],[%TIME%].LAG(12)))' SOLVE_ORDER=3

[Former Member · Accepted solution]

Hi Vadim,

In the formula u mentioned [MEASURES].[/CPMB/SDATA].

But for R12 earlier you mentioned we have to use [MEASURES].[PERIODIC] or [MEASURES].[YTD].

I am tried with the code you provided, system respond quickly, but the values are not correct:

Here is the sample test data:

2015.05           --> 10  YTD Values          --> 10 Periodic Value

2015.06           --> 21  YTD values           --> 11 Periodic Value

2015.07           --> 33  YTD values        --> 12 Periodic Value

2015.08           --> 46  YTD values        --> 13 Periodic Value

2015.09           --> 60  YTD values        --> 14 Periodic Value

2015.10           --> 75  YTD values        --> 15 Periodic Value

2015.11           --> 91  YTD values        --> 16 Periodic Value

2015.12           --> 108  YTD values       --> 17 Periodic Value

2015.TOTAL    --> 108  YTD values

2016.01           --> 18  YTD values        --> 18 Periodic Value

2016.02           --> 37  YTD values        --> 19 Periodic Value

2016.03           --> 57  YTD values        --> 20 Periodic Value

2016.04           --> 78  YTD values        --> 21 Periodic Value

R12 -->  2016.04  = 186 as per logic (Sum(21+20+ ....+10)

But R12 formula, system gave result of 522.

Didnt understand how it calculate the value of 522?

The reason for it is, we used [MEASURES].[/CPMB/SDATA].

I have replaced above formula with [MEASURE].[YTD] instead of [MEASURES].[/CPMB/SDATA].

After changing this i am getting correct value of 186 and performance improved from 6 minutes to 20 seconds.

Thanks for the help.

[former_member186338 · Accepted solution]

Not clear, the formula has to work correctly with [MEASURES].[/CPMB/SDATA] for the cube with YTD storage!

In your sample you didn't provided the YTD value for 2015.04, but looks like it's zero.

Then my formula will calculate:

78+108-0=186

Same as 10+...+21=186

Please provide you measure formula.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

You are right.

I dont have 2015.04 values so 2015.04 = 0.

And the calculation you mentioned are correct 186 for 2016.04.

Here is the measure formula after modified with [MEASURES].[YTD].

MEMBER [MEASURES].[YTD] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])))' SOLVE_ORDER=3

MEMBER [MEASURES].[PERIODIC] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JUL" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

MEMBER [MEASURES].[R12] AS

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

-([MEASURES].[YTD],[%TIME%].CURRENTMEMBER)-([MEASURES].[YTD],[%TIME%].LAG(12).PARENT.PARENT)+([MEASURES].[YTD],[%TIME%].LAG(12)),

([MEASURES].[YTD],[%TIME%].CURRENTMEMBER)+([MEASURES].[YTD],[%TIME%].LAG(12).PARENT.PARENT)-([MEASURES].[YTD],[%TIME%].LAG(12)))' SOLVE_ORDER=3

I will test few scenarios with 2015.04 having values to check the result.

Thanks.

[former_member186338 · Accepted solution]

Ups, I have corrected measure formula and have proper result in the test system!

Formula (without any YTD and PERIODIC!):

'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC"),-([MEASURES].[/CPMB/SDATA],[%TIME%].CURRENTMEMBER)-([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%],[%TIME%].LAG(12).PARENT.PARENT))+([MEASURES].[/CPMB/SDATA],[%TIME%].LAG(12)),([MEASURES].[/CPMB/SDATA],[%TIME%].CURRENTMEMBER)+([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%],[%TIME%].LAG(12).PARENT.PARENT))-([MEASURES].[/CPMB/SDATA],[%TIME%].LAG(12)))' SOLVE_ORDER=3

And it's fast!

Vadim

P.S.

CLOSINGPERIOD will return last month (%TIMEBASELEVEL%) of the YEAR.TOTAL parent !

[Former Member · Accepted solution]

Hi Vadim,

Yes this new code is fast and simple, without any YTD and Periodic calculations in it.

In my Periodic Model I have used similar code without any YTD and Periodic calculations so it is fast response time.

Thanks.

[former_member186338 · Accepted solution]

Always test the solution you want to propose

I forgot that MDX know nothing about YTD model!

Then YEAR total is calculated as a sum of YTD values:

10+21+33+46+60+75+91+108=444

78+444=522!