Skip to Content

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

SQL_BIDS

 

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.

BIDS_Open

Pick_DB

 

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

App_Browse

 

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

Calculations

 

Fill out the Name and the Expression as specified below:

Measure

 

To save the formula, click on the Process button, three green arrows in a circle or from the menu
(Build -> Process)

BIDS_Process

Click Run.

 

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

BIDS_Process

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

 

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

Pivot

 

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:

SQL_MGMT_Studio

 

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

 

SQL_Tables

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.

 

BPC_Modify

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

 

BPC_Modify_2

 

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

 

BPC_MS_Final

 

 

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 (3rd from the left)

 

SE38

 

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

 

Appset_Parameters

 

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.

 

BPC_NW_Final

25 Comments
You must be Logged on to comment or reply to a post.
  • This is something that almost every client asks for and now we can deliver very easily.  Thanks for taking the time to educate us all.
  • Glad to see you posting on SCN, Rob! 

    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.

  • Hi

    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

  • 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 Nick,  Just curious, do you see much performance degradation in the custom measures?  I find many users want this ability, but it’s not consistent across the entire user base.   Jim
      • 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

  • 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

  • /
    ROLLING12.PNG
    • 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

    • 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

      • /
        ROLLING12.PNG
        • 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

          • 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

  • 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?