Technical Articles
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.
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..
Thanks
Ravi
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.
Thanks Bhargavi
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
Ravi
Hi Ravi
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?
Thanks
Bhargavi.
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.
Regards,
Iqubal
Please use YTD calculation above. Then you can use both the Amount value along with calculated measure in the same story.
Thanks
Ravi
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.
Hi
This can be achieved by subtracting Year totals with YTD for current month. create Calculated Measures for Year total using below.
YEAR_TOTAL
Read more here.
https://blogs.sap.com/2023/03/07/working-with-dynamic-time-navigation-functions-in-sap-analytics-cloud/
Balance in Rest of the Year
Hope this helps.
Thanks
Ravi
an excellent blog providing flexible ways of calculating the "X"TD calculations. Thanks Ravi!
Hi Ravi,
This is super helpful and convenient.
My only issue is, for getting the calculation its requires to have all year periods in the story, otherwise I'm not getting the right result.
It is not behaving as other model calculations, because it calculates based on what you are showing in the story (if I filter Q2, for example, is doing the YTD from April onwards, even though the formula specifies January) .
Am I missing something?
Thanks a lot for your help,
Iria
Excellent, Ravi; a blog provides to calculate YTD and QTD calculation.
How can we add a quarter in the YTD calculation, so we can get the correct number in quarters?
When we are using hierarchy in the Date (time Dimension),
Thank you in advance
NT
Hi Ravi,
This is super helpful blog. I am wondering if the Iterate & Prior can be used to get the YTD value of the prior month as a separate calculated Model KF.
thank you
Dieter
Hello,
Great blog! Thanks for the information. One quick question, what if we don't have the time axis whitin the report? I'm trying to build a report based on several restricted measures (each one pointing to different months) and it's not working (it's showing the value for january in all the cases)
Thanks!