# Rolling 12 Custom Measure in BPC 7.x, version for MS and NW

It is not out of the ordinary for a client to want a summation of last 12 months for many accounts. One way of doing this, if the number of accounts is minimal, is to create several dimension formulas, but this could diminish performance. A better way of accomplishing this is to create a Custom Measure called Rolling 12.

**BPC version for Microsoft**

In BPC, version for Microsoft, it is fairly straight forward in that the formula for both Periodic and YTD are the same, except for “ELSE” statement at the end.

The formula for a Periodic application is as follows:

iif([Account].CurrentMember.Properties(“ACCType”)=”INC” or

[Account].CurrentMember.Properties(“ACCType”)=”EXP”,sum(LastPeriods

(12,closingperiod([Time].month)),Measures.[Periodic]),Measures.[Periodic])

For a YTD application:

iif([Account].CurrentMember.Properties(“ACCType”)=”INC” or

[Account].CurrentMember.Properties(“ACCType”)=”EXP”,sum(LastPeriods

(12,closingperiod([Time].month)),Measures.[Periodic]),Measures.[YTD])

To test the formula, login into SQL Server Business Intelligence Development Studio

Select File-> Open -> Analysis Services Database. When the splash screen renders, select the correct database. If a database is not listed, enter the correct server and database.

On the right side of the pane in the Solution Explorer Pane, select the desired cube, right click, and select Browse.

Click on the Calculations Tab and in the script Organizer, right click and select New Calculated Member.

Fill out the Name and the Expression as specified below:

To save the formula, click on the Process button, three green arrows in a circle or from the menu

(Build -> Process)

Click Run.

When the process is finished, click Close and Close again.

Navigate back to the Browser Tab and reconnect to the database by clicking on the icon.

Drag the Measures dimension into the center of the table, Time along rows, and Account for the columns.

You will notice that the YTD and the new Rolling 12 measure should equal for the current year, but will be different once you span multiple years.

In order to get this into BPC, you must log into SQL Server Management Studio:

Navigate to the proper Application Set -> Tables -> dbo.MeasureFormula. Right click and choose Edit Top 200 Rows.

Fill out the table according to the below:

- SEQ: Pick a number greater than what is already stored by 10, in my

instance I chose 270 - Name: The name of the Custom Measure, in this case it is Rolling_12
- Statement: enter

[Measures].[Rolling_12] as ‘iif([%

ACCOUNTDIM%].CurrentMember.Properties(“ACCType”)=”INC” or [%

ACCOUNTDIM%].CurrentMember.Properties(“ACCType”)=”EXP”,sum

(closingperiod([%TIMEDIM%].month,[%TIMEDIM%].currentmember).lag

(11):closingperiod([%TIMEDIM%].month,[%

TIMEDIM%].currentmember),Measures.[Periodic]),Measures.[Periodic])

- SolveOrder: 4
- Description: Rolling_12
- Required Level: Year
- IsVisible: 1
- IsYTDApp: 1 for yes or 0 for Periodic
- IsSystem: 1

Tab out of the last column to save the row. Right click on dbo.MeasureFormulaApp and Edit the Top 200 Rows. Scroll to the bottom and enter the below:

- Application Name: Finance or your cube name
- SEQ: 270, or the number you specified earlier
- Name: Rolling_12
- Description: Rolling 12

The last step is to log into BPC Admin and reprocess the cube. To do this, login to BPC Administration, go to the applications, and highlight Application. On the right, click Modify Application.

Select the appropriate application and check the boxes to Reassign SQL Index and Process Application. Next, click Modify Application.

When this is complete, log into BPC for Excel and in the Measures dimension, you should now see Rolling_12.

**For BPC 7.x, version for Netweaver**

Login to the SAP GUI and go to transaction SE38. For the Program, enter UJA_MAINTAIN_MEASURE_FORMULA and click the Execute icon (3^{rd} from the left)

Enter the Application Set ID, Application ID, and the User ID:

Click the green check mark.

Click on the first icon to create a new measure. Enter Formula Name, Description, and formula.

For Periodic applications, the formula is as follows:

MEMBER [MEASURES].[ROLLING12] AS ‘IIF([%

P_ACCT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)=”INC”,SUM

(LastPeriods(12,CLOSINGPERIOD([%TIME%].[LEVEL02])),-[Measures].

[/CPMB/SDATA]),IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES

(“2/CPMB/ACCTYPE”)=”EXP”,SUM(LastPeriods(12,CLOSINGPERIOD([%

TIME%].[LEVEL02])),[Measures].[/CPMB/SDATA]),IIF([%

P_ACCT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)=”AST”,

([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02])),IIF

([%P_ACCT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)=”LEQ”,-

([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02])),-

[MEASURES].[/CPMB/SDATA]))))’;SOLVE_ORDER=3

For YTD applications, the formula is as follows:

MEMBER [MEASURES].[YTD] AS ‘IIF(([%

P_ACCT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)=”INC” OR

[%P_ACCT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)=”LEQ”),-

([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])),

([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])))’

SOLVE_ORDER=3##MEMBER [MEASURES].[PERIODIC] AS ‘IIF(([%

P_ACCT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)=”INC” OR

[%P_ACCT%].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].[ROLLING12_YTD] AS ‘IIF([%

P_ACCT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)=”INC” OR

[%P_ACCT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)

=”EXP”,SUM(LASTPERIODS(12,CLOSINGPERIOD([%TIME%].[LEVEL02])),

[MEASURES].[PERIODIC]),[MEASURES].[PERIODIC])’;SOLVE_ORDER=3

*****Note**– The Rolling 12 custom measure for a YTD application is

more complex because it has to build off the Periodic calculation

which is built off the YTD calculation.

Click the Save Icon.

Login to BPC for Excel, and you should be able to see the new custom measure.

Thank you for sharing this.

While we don't currently have a need to utilize this, it's nice to know that if/when we do, we'll know how.

First of all, thak you for your blog

I am trying to use the MDX measure script for a Periodic application in a 7.5 NW enviroment but the script is not working properly.

Is showing random results.

Is there any way to check the script, like transaction MDXTEST?

Thanks a lot

Pablo Goldmann

Please ensure the ACCType property is setup correctly and Note 1459126 has been applied.

Thanks again for you fast answer!!!!

Regards

Hi Robert,

Thanks for this. Some of our users want to see credits (INC / LEQ) as a negative in reports, so I have used this framework to make another measure which just ignores account type checking, both for Periodic and YTD.

(SAP BPC 7.0 for MS)

Thanks for the post.

Ta

Nick

Hi James,

I've just got it working on a testbed. Give me a few days to see if there's an impact on the live server. Our FACT database is only about 2GB on an SSD, so I would be surprised if there is a noticeable change.

However, I will keep you posted either way

BTW, I assume the 'Modify Application' won't mess with anything that was working already. I see that there are lines that say 'Create Stored Procedures and Comment Table ... done in 0:00' and 'Make OLAP database and Journal / Audit reports / Validate Dimension Formulas ... done in 2:29' I assume that's all safe? (Can't seem to find much documentation on what happens behind the scenes)

Ta

Nick

Jim,

Custom Measures will not give you as much of a performance issue on the MS platform as on NW.

Thanks,

Rob

For BPC 10 NW, Try this code as the code mentioned above has some issues in BPC 10.

'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM(LastPeriods(12,CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[Measures].[/CPMB/SDATA]) ,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(LastPeriods(12,CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),[Measures].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

Hi,

I'm trying to implement rolling 12 to BPC 10.0 NW environment, but I get the following error. What causes this?

Tommi

Tommi,

try removing 'MEMBER[MEASURES].[ROLLING12] AS' from this code, this is not needed in BPC 10 NW. Try creating a new measure and delete this one, use code :

'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM(LastPeriods(12,CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[Measures].[/CPMB/SDATA]) ,IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(LastPeriods(12,CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),[Measures].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

Hi,

I removed MEMBER[MEASURES].[ROLLING12] AS' but I still get the same error.

Tommi

Tommi,

I entered this and it worked...

'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM(LASTPERIODS(12,CLOSINGPERIOD([%TIME%].[LEVEL02])),-[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(LASTPERIODS(12,CLOSINGPERIOD([%TIME%].[LEVEL02])),[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

The reason you are getting that error is because Measures (7.0 and 7.5) is different than MEASURES (10).

When you go into the EPM Add-in, you may need to refresh your connection Clear your Metadata Cache (under the More menu) as well as "Refresh Metadata for Current Connection".

Let me know how it goes.

Rob

Hi,

I still get the same error.

Tommi

Tommi,

What is your email, and I am happy to help.

FYI...if anyone would like the ROLLING_12 for HANA with ENABLE_HANA_MDX turned on, this is the formula...the key difference is the space in the Time Level (LEVEL02 vs LEVEL 02)

'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM(LASTPERIODS(12,CLOSINGPERIOD([%TIME%].[LEVEL 02])),-[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(LASTPERIODS(12,CLOSINGPERIOD([%TIME%].[LEVEL 02])),[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

Thanks,

Rob

You may want to try deleting this measure completely and start afresh.

Hi,

Problem is that nothing is saved to BW, so I can't see my custom formula in the table (and there is nothing to delete).

Alternative solution is to create e.g. new category member and use member formula to calculate it. But it would be better to get this to work.

Tommi

Tommi,

I tend to agree with Kamlakant, get completely out of the SAP GUI and start over. Also, you might want to run (SE38) the program UJA_REFRESH_DIM_CACHE.as well as the program UJXO_CLEAN_CACHE before starting again.

Rob

Hi,

I exited GUI and run UJA_REFRESH_DIM_CACHE (UJXO_CLEAN_CACHE program did not exist), but I still get the same error.

Tommi

I've added the formulas as described above and modified the applications, but I don't have the rolling 12 month option show up in my selector in Excel. It looks that there are other calculations that don't show up but are designated as visible in the dbo.MeasureFormulaApp (such as load sign and half year to date). Is there some other configuration that needs to occur?

I have added next code in SAP BPC 10.1 NW and it does not work correctly:

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].[LTM] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(LASTPERIODS(12,CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),[MEASURES].[PERIODIC]),[MEASURES].[PERIODIC])';SOLVE_ORDER=3

Values get on Excel are not the proper ones. What is more, if I get in the same report the PERIODIC and LTD members, the PERIODIC member gets worng values. However, if I get only PERIODIC member in a report, the values retrieved are ok. Any idea? Do you think previous code is ok?