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

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

Assigned Tags

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

      Thanks

      Ravi

      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?

      Thanks

      Bhargavi.

      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.

      Regards,

      Iqubal

      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.

       

      Thanks

      Ravi

      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

      Hi

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

      YEAR_TOTAL

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

      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

      ITERATE(PRIOR()-[VALUE],[YEAR_TOTAL], [d/Date].[p/CALMONTH])

       

      Hope this helps.

      Thanks

      Ravi

       

       

      Author's profile photo Yao Yao
      Yao Yao

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