{"id":68,"date":"2011-04-18T20:24:00","date_gmt":"2011-04-18T20:24:00","guid":{"rendered":"http:\/\/www.sapbpc.sapag.co.in\/?p=68"},"modified":"2011-04-18T20:24:00","modified_gmt":"2011-04-18T20:24:00","slug":"rolling-12-custom-measure-in-bpc-7-x-version-for-ms-and-nw","status":"publish","type":"post","link":"https:\/\/www.sapbpc.sapag.co.in\/?p=68","title":{"rendered":"Rolling 12 Custom Measure in BPC 7.x, version for MS and NW"},"content":{"rendered":"

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.<\/p>\n

BPC version for Microsoft<\/strong><\/p>\n

In BPC, version for Microsoft, it is fairly straight forward in that the formula for both Periodic and YTD are the same, except for \u201cELSE\u201d statement at the end.<\/p>\n

The formula for a Periodic application is as follows:<\/p>\n

          iif([Account].CurrentMember.Properties("ACCType")="INC" or<\/p>\n

          [Account].CurrentMember.Properties("ACCType")="EXP",sum(LastPeriods<\/p>\n

          (12,closingperiod([Time].month)),Measures.[Periodic]),Measures.[Periodic])<\/p>\n

For a YTD application:<\/p>\n

         iif([Account].CurrentMember.Properties("ACCType")="INC" or<\/p>\n

         [Account].CurrentMember.Properties("ACCType")="EXP",sum(LastPeriods<\/p>\n

         (12,closingperiod([Time].month)),Measures.[Periodic]),Measures.[YTD])<\/p>\n

To test the formula, login into SQL Server Business Intelligence Development Studio<\/p>\n

\"SQL_BIDS\"<\/p>\n

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.<\/p>\n

\"BIDS_Open\"<\/p>\n

\"Pick_DB\"<\/p>\n

On the right side of the pane in the Solution Explorer Pane, select the desired cube, right click, and select Browse.<\/p>\n

\"App_Browse\"<\/p>\n

Click on the Calculations Tab and in the script Organizer, right click and select New Calculated Member.<\/p>\n

\"Calculations\"<\/p>\n

Fill out the Name and the Expression as specified below:<\/p>\n

\"Measure\"<\/p>\n

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

\"BIDS_Process\"<\/p>\n

Click Run.<\/p>\n

When the process is finished, click Close and Close again.<\/p>\n

\"BIDS_Process\"<\/p>\n

Navigate back to the Browser Tab and reconnect to the database by clicking on the  \"Reconnect\" icon.<\/p>\n

Drag the Measures dimension into the center of the table, Time along rows, and Account for the columns.<\/p>\n

\"Pivot\"<\/p>\n

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.<\/p>\n

In order to get this into BPC, you must log into SQL Server Management Studio:<\/p>\n

\"SQL_MGMT_Studio\"<\/p>\n

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

\"SQL_Tables\"<\/p>\n

Fill out the table according to the below:<\/p>\n