Skip to Content
Technical Articles
Author's profile photo ravi akasapu

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]  )


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..



Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Muhammad Shafiq
      Muhammad Shafiq

      Thanks for your job you have done, Very Informative

      Author's profile photo Bhargavi Gali
      Bhargavi Gali

      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.

      Author's profile photo ravi akasapu
      ravi akasapu
      Blog Post Author

      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.



      Author's profile photo Bhargavi Gali
      Bhargavi Gali

      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?



      Author's profile photo md ahmed
      md ahmed

      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.



      Author's profile photo ravi akasapu
      ravi akasapu
      Blog Post Author

      Please use YTD calculation above. Then you can use both the Amount value along with calculated measure in the same story.




      Author's profile photo PCC - Business Intelligence PCC - Business Intelligence
      PCC - Business Intelligence PCC - Business Intelligence

      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.

      Author's profile photo ravi akasapu
      ravi akasapu
      Blog Post Author


      This can be achieved by subtracting Year totals with YTD for current month. create Calculated Measures for Year total using below.


      LOOKUP([VALUE],[d/Date].[p/YEAR]= Current("Year"))

      Read more here.


      Balance in Rest of the Year



      Hope this helps.





      Author's profile photo Yao Yao
      Yao Yao

      an excellent blog providing flexible ways of calculating the "X"TD calculations. Thanks Ravi!

      Author's profile photo IRIA LÓPEZ MONTESINOS VÁZQUEZ

      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,


      Author's profile photo NAVENDU TRIPATHI

      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



      Author's profile photo Dieter Zenger
      Dieter Zenger

      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



      Author's profile photo Andres Romero
      Andres Romero


      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)