YTD Measures in SAP Analytics Cloud
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.
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.
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.
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
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] )
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..
Thanks for your job you have done, Very Informative
Thank You for the wonderful blog. I just have a small doubt. Can this YTD measure be calculated based on the input control we add in the story?
If I add an input control of Month in the story then can my YTD calculation consider the month I select in the input control.
The measures are based on calculations at Model level. We can re-use the calculations in any story and it still calculates as expected.
Thanks for your reply. I have tried creating the above formula in my model but I am getting the error "Failed to fetch data" even though the formula is correct.
Do you have any idea?
I have a requirement wherein I need to show the YTD values upto the selected period in a column as well as the monthly value of the selected month. This can be done using restricted measures in stories but I was wondering if this can be achieved at model level also as I need to use the same report in SAC addin also. I have tried with lastmonths but it doesn't work as there is no way to control the upto current period settings.
Please use YTD calculation above. Then you can use both the Amount value along with calculated measure in the same story.
Thank you so much for this wonderful Blog.
Could you please help me in calculating BOY ( Balance of Year )
Like for Current Fiscal Year if YTD is Jan 2023 till March 2023
then How can we calculate BOY which is from March 2023 till Dec 2023.
Thanks a ton. Looking forward for your reply.
This can be achieved by subtracting Year totals with YTD for current month. create Calculated Measures for Year total using below.
Read more here.
Balance in Rest of the Year
Hope this helps.
an excellent blog providing flexible ways of calculating the "X"TD calculations. Thanks Ravi!