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 (3rd 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.
- SAP Managed Tags:
25 Comments
