Calculate Current Month, Last year same month, Previous Month, YTD, for [Cross Calculation] on SAC

[Charlielin]

Hi Experts,

  I am facing a requirement where I need to calculate Input Month, Last year Input month, Previous Month, YTD of income statement figures(SAP COPA Data) all on the same report using SAC(as below), the source data is from Datasphere.

 Cross Calculation is used

User will specify an input [Year/Month] at run-time. 

Please share your experiences on how to achieve this, thanks a lot !!!

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Q2: We have to calculate the weighting for each line, is that possible ?

[Mark_P_Burke]

@Charlielin. Use the %GrandTotal() or GrandTotal() Mathematical Functions.

[Mark_P_Burke · Accepted solution]

@Charlielin The free Rapid FP&A Suite from SAP has an example of a story that does this. The user selects a Month (e.g., September 2024) and can then get the MTD, QTD, and YTD values for this year and last year.

The key is the configuration and layout of the restricted measures. You can also create a restricted measure to create a prior month value.  I took a screenshot of the data selection and a couple of examples of the restricted measures, but the Rapid FP&A download shows you how it is all put together 100% in a sample solution from SAP.  You can basically browse the story and see it working and use what you need!

Note that on each restricted measure in the attached images, you will see a "visual" cue of what time period is being shown, all based on the single Selection Date (September 2024). You can change that Select Date to another month and get relevant results for that date, allowing you to "step back in time."

It is in the Rapid Finance (FPA) model and in the Dashboard story in the second tab, which gets delivered with a model, stories, restricted measures, and sample data so you can see how it works and apply it to your model. We also have a variation of the same concept in the Rapid Sales model—Dashboard Story—Sales Analysis in tab 2.

We have a new version coming out on August 19, 2024, so I suggest waiting to download it.  Download instructions for best practice content Rapid FP&A are HERE

Thanks,

Mark P Burke, SAP

[Charlielin · Accepted solution]

Thanks for your solution, it's really helpful. Now I come across with another tricky one which i need to calculate the "Weighting" for each line(I've added to my post - Q2) is that possible to achieve it ?

[Mark_P_Burke · Accepted solution]

In Q2: I am not sure what is in Measure A, Measure B, and Measure C, but those likely should be in a Standard Account dimension so that you can use Account formulas. It seems like you may be trying to do row-based calculations in Measures, but Measures in SAC are meant for Column-based calculations. I don't know your full use case.

[Charlielin · Accepted solution]

Yes, we are trying to do row-based calculation so called "Cross Calculation" in Optimized design experience in SAC. A/B/C measures are key figures representing different kind of KPIs. Customer requested the format in this way so we are figuring out if it's possible or not

[Mark_P_Burke]

For Q2, use the %GrandTotal() or GrandTotal() Mathematical Functions.  See SAC Help.Sap.com
If this is all relative to Net Income as total, then divide each row by Net Income.

GrandTotal(Account)

Returns the grand total of all the Account values in the result set. Filters are included in the calculation of the grand total.

GrandTotal(Sales) returns the aggregated value of Sales.

%GrandTotal(Account)

Returns the percentage of the grand total that each value represents.

%GrandTotal(Sales) returns the percentage of the grand total that each value represents.

[Charlielin]

Apologized that I've given a bad example, the actual situation is that we have to use "Cross Calculation" to populate different measures on the row instead of just a single [account] dimension, that's why it's kinda complicated

[Charlielin]

I've refreshed my screenshot to make it more clear

[Savio_Dmello]

You can achieve it using Input Controls, Dynamic Filtering, and Calculated Measures or Dimensions.

[N1kh1l]

@Charlielin 

Your Input Year month should be Date filter. Rest all can accomplished using dynamic time restricted measures.

Previous Month: Make the selection as below

Previous Year Input Month: 

YTD:

Hope this helps !!

Nikhil

[Charlielin]

Thanks for replying, however users will dynamically enter a Year/Month instead of just using the system date. Currently, I managed to achieve this by creating Input Parameters + set flags dynamically based on user input for Calculated Column