SAC Data Action Advanced Formula - Calculate Average amount and percentage of Past Periods

[navendut]

Hello SAP Guru’s

I want to define a calculation in data action that should calculate the previous month's average amount and average percentage (%) based on user input. Please refer to the screenshot below.

Here, variable "n" is user input. So, if the user inputs n=3, the logic should give the average of the last 3 months. If the month is September, then the average amount and percentage (%) of June, July, and August should be calculated. The December, January, and February average amount and percentage (%) should be calculated if the month is March.

Anyone who has faced a similar kind of issue knows the workaround in this case. Their comments would be deeply appreciated.

Thank you in advance.

Regards,

Navendu T

[N1kh1l · Accepted solution]

navendut

navendutConsidering your current example, the below should do the job. I have assumed , September was current month and different measures were used to store the Average and Average %

Original Amount: Nominal is my Account dimension.

Advance Formula Code

N is the parameter for lookback months

MEMBERSET[d/Measures]="TR_AMOUNT"
MEMBERSET [d/Date]="202306" to "202404"
MEMBERSET [d/Nominal]=("ACCT_01","ACCT_02")

INTEGER @COUNTER
INTEGER @LOOKBACK
INTEGER @CURRENTMONTH
INTEGER @CURRENTYEAR
VARIABLEMEMBER #TOTAL OF [d/Nominal]
@CURRENTMONTH=MONTH(TODAY())
@CURRENTYEAR=YEAR(TODAY())
@LOOKBACK=%N%

// -----------------Average Calculation---------------------------------
IF MONTH([d/Date])>=@CURRENTMONTH OR YEAR([d/Date])>@CURRENTYEAR THEN
	DATA([d/Measures]="AVERAGE")=0.0
		FOR @COUNTER = 1 TO @LOOKBACK
			
		DATA.APPEND([d/Measures]="AVERAGE")=RESULTLOOKUP([d/Measures]="TR_AMOUNT",[d/Date]=PREVIOUS(@COUNTER))/%N%
	
	ENDFOR
	// -----------------Average % Calculation---------------------------------
	DATA([d/Nominal]=#TOTAL,[d/Measures]="AVERAGE")=RESULTLOOKUP([d/Measures]="AVERAGE")
	DATA([d/Measures]="AVERAGE_PERC")=RESULTLOOKUP([d/Measures]="AVERAGE")/RESULTLOOKUP([d/Measures]="AVERAGE",[d/Nominal]=#TOTAL)
ENDIF
 

Output:

Hope this helps !!

Br.

Nikhil

[navendut · Accepted solution]

Perfect! Thank you soo much, Nikhil!

[symega2021_92 · Accepted solution]

Hi Nikhil,

I would like to know Average ,AVERAGE_PERC measures are directly coming from source or created calculated measure in Model or Story ?

[N1kh1l]

navendut

Before I answer your query, I would like to understand how are you storing the main Account balances and Average and the Average % . From you example its not clear, they are different measures, or differentiated by another dimension like Auditrail etc. I see the Opex accounts 1 and 2 and their parents, so it has to be sperate measure/ or another dimension member

You have shown time period Jun23 to Apr 24 ( fiscal), so If N =4 then it should do last 4 months Average but when you say the month is September, you mean the current calendar month or you took September as your N =3 and period starts from June . If the month is September and N=4 what should happen in your example , will you consider May 23 in calculating the average ? Also You show 2 accounts Accounts 1 and 2 in a hierrachy node under OPEX, do you have the hierarchy or you just showed it for representation. For Average % you are using the Hierrachy node value, hence this becomes important.

What should be the horizon for the calculation. Your example shows only till Apr 24 but what happens to the calculation beyond that ? is there a version.start and end period governing this ?

Nikhil

[vishalakshmi]

Hello Navendu,

I hope the below procedure will help you:

Assuming you have a dataset with columns for "Month," "Amount," and "Percentage," and you want to create calculated measures for the average amount and average percentage for the previous 'n' months based on user input:

Create a variable "n" in SAP Analytics Cloud that allows the user to input the number of previous months to consider.

Create a calculated measure for the average amount:

Name it "Average Amount (n Months)."

Use the following formula to calculate the average amount:

AVG({[Month].CurrentMember.Lag(n - 1):[Month].CurrentMember}, [Amount])

This formula calculates the average of the "Amount" for the last 'n' months based on the user input.

Create a calculated measure for the average percentage:

Name it "Average Percentage (n Months)."

Use the following formula to calculate the average percentage:

AVG({[Month].CurrentMember.Lag(n - 1):[Month].CurrentMember}, [Percentage])

This formula calculates the average of the "Percentage" for the last 'n' months based on the user input.

Now, when the user inputs a value for "n," these calculated measures will automatically adjust to calculate the average amount and average percentage for the previous 'n' months.

Make sure to adjust the dimension and measure names in the formulas based on your actual dataset's column names and dimension hierarchy. So this approach allows you to dynamically calculate the averages based on the user's input for the number of previous months to consider.

Thanks,

Lakshmi.

[navendut]

Thank you. It is helpful.

[vishalakshmi]

Hello Navendu,

Can you please up-vote? If you are satisfied with my answer?

Thanks,

Lakshmi.