While creating Plans and Budgets or analysing data over months, Year-to-Date and Quarter-to-Date are very useful measures that can provide different views of the data and insights. BPC as a standalone tool provides these measures out of the box and can be customised with ACCOUNT dimension depending on the ACCTYPE. It will also help provide certain type of planning scenarios like Rolling Forecast with 12 month forward looking using Measures alone.
With SAC this is not possible as standard. We can use Calculated Measures created at Model level to achieve the YTD and QTD. It is also possible to customise the Calculated Measures using ACCTYPE property in ACCOUNT dimension to report with different account calculations.
There is an excellent article by Yao Yao on how to create Calculated Measured for Cumulative sum. Check the blog post here.
More references at ITERATE Formula and SAP Help Documentation
YTD Calculated Measure
The Year-to-Date Calculated Measures are created on top of the existing or imported measures that are available in the Model. The new calculated measures will use the existing measures along with Date/Account dimension members as parameters to derive the YTD results.
YTD Calculated Measure
The new measure makes use of ITERATE and PRIOR functions and calculate the result on top of Measure AMOUNT. The above calculation assumes the month “Jan” as the start of the year. Each time the formula initialises the result to “0” for January and cumulatively adds each month data, iterated by CALMONTH property from Date dimension.
ITERATE(IF([d/Date].[p/MONTHDESC]="Jan" ,0 , PRIOR () ) + [AMOUNT], [AMOUNT], [d/Date].[p/CALMONTH])
QTD Calculated Measure
Similar to YTD calculation above, we can derive the data for Quarter-to-Date with slight change in the formula. Instead of initialising the Sum value for January, the ITERATE function initialises for Jan, Apr, Jul and Oct for each of the Quarter. This will result in cumulative sum for each quarter.
QTD Calculated Measure
ITERATE(IF([d/Date].[p/MONTHDESC]=("Jan" OR "Apr"OR "Jul" OR"Oct") ,0 , PRIOR () ) + [AMOUNT], [AMOUNT], [d/Date])
YTD Calculated Measure with ACCTYPE
Similar to Measures in SAP BPC where the Cumulative sum changes depending on the the ACCTYPE of the ACCOUNT dimension, we can create additional measures for both YTD and QTD for different account types like AST, LEQ, INC and EXP.
YTD Calculated Measure with ACCTYPE
ITERATE(IF([d/ACCOUNT].[p/accType] = ("INC" OR "EXP"), IF([d/Date].[p/MONTHDESC]="Jan" ,0 , PRIOR () )+[AMOUNT],[AMOUNT]), [AMOUNT], [d/Date].[p/CALMONTH] )
QTD Calculated Measure with ACCTYPE
QTD Calculated Measure with ACCTYPE
ITERATE(IF([d/ACCOUNT].[p/accType] = ("INC" OR "EXP"), IF([d/Date].[p/MONTHDESC]=("Jan" OR "Apr"OR "Jul" OR"Oct") ,0 , PRIOR () )+[AMOUNT],[AMOUNT]), [AMOUNT], [d/Date].[p/CALMONTH] )Results
Advantages and Limitations
The advantage with using calculated measures at Model is the reusability. We can use same measures in multiple stories and across all the versions. We can control the Calculations at Model level and can create/Modify the formulas without going into each story.
But this will present limitations like.
- We need to create the YTD and QTD measures for each of the Measure to use in the stories. This might make models complicated if there are more measures in the Model like UNITS or PRICE. This might add to complexity.
- The calculations will only work on the base members in Date dimension. Even though the calculation results work correctly as expected in individual months, the totals at Year or Quarters still follow the SUM Aggregation at the nodes. This may not be correct for calculation with ACCTYPE in the formulas.
Considering both advantages and limitations, it is worth creating some of the calculated measures that are needed in most of the stories as base line model. Both ITERATE and PRIOR along with Dimensions and Properties will allow us to create more Calculations with various complexities..
