SAC - Advanced Formula Data Action - Calculate Prior Previous Year Ending Balances.

[RameshBandi]

Dear Experts,

We are currently facing a challenge in SAP Analytics Cloud concerning data action using advanced formulas, specifically regarding the calculation of previous years' figures within the Account and Date dimensions. We are utilizing an Account-Based Model perform forecasts on our Balance Sheet Model.

Requirement:

Our objective is to calculate the Prior Year Average Balance for future periods based on actual data. The formula we would like to implement is as follows:

=(PY-1 End Bal + PY End Bal)/2

 Example:

For example, when we are on **January 2025**, our calculation should reference the following values:

If I am in any month in any month of current year, it has to pick last year Dec Ending values of Accounts Payable data from Account Dimension – Prior Previous Year Dec Data for Account Payable data from Account Dimension.

Chart:

2023.Dec	2024.Jan	2024.Feb	2024.Mar	2024.Apr	2024.May	2024.Jun	2024.Jul	2024.Aug	2024.Sep	2024.Oct	2024.Nov	2024.Dec	2025.Jan
10	12	13	15	15	15	20	20	25	25	30	35	40	50 (Expected)

- **2024 Dec** (Current Year End Balance)

- **2023 Dec** (Prior Year End Balance)

Thus, the expected result for **2025 Jan** would be:

On Jan 2025 When I run data action it should calculate : =(PY-1 End Bal + PY End Bal)

2024.Dec + 2023

40               +    10  Expected Results = 50 on 2025.Jan

Current Approach:

At present, I am utilizing the `PREYEARLAST` function to retrieve last year's ending data. However, I am encountering difficulties in formulating a straightforward syntax to fetch the data from the Prior Previous Year.

To address this, I have implemented the following workaround, although I have concerns regarding its efficiency:

IF [d/Date].[p/MONTHDESC] = "Jan" THEN

  DATA([d/Account]="Stat_PYAvgBal_AP") =   (RESULTLOOKUP([d/Account]="Stat_AP", [d/Date]=PREYEARLAST([d/Date])) +

    RESULTLOOKUP([d/Account]="Stat_AP", [d/Date]=PREVIOUS(13, "MONTH", [d/Date]))) / 2

```

This approach retrieves data for all 12 months of the current year while going back 24 months, which may not be optimal.

Request for Assistance:

I would greatly appreciate any insights or recommendations on achieving this calculation in a more efficient manner. Your expertise in this area would be invaluable.

Thank you for your attention to this matter.

Regards,

Ramesh

[MoonJun]

Hi @RameshBandi 

You can eliminate the "IF [d/Date]. "[p/MONTHDESC] = "Jan" THEN" condition for more data action script flexibility in the below script. 

MEMBERSET [d/Date] = "202501"

INTEGER @Month
@Month = MONTH([d/Date]) + 12

DATA([d/Account] = "Stat_PYAvgBal_AP") = (RESULTLOOKUP([d/Account] = "Stat_AP", [d/Date] = PREYEARLAST([d/Date])) +
										  RESULTLOOKUP([d/Account] = "Stat_AP", [d/Date] = PREVIOUS(@Month, "MONTH", [d/Date]))) / 2

If you want to calculate it only for the Start Period of Plan version, 

• Start_Period: 202501
• End_Period: 202512

MEMBERSET [d/Date] = [d/Version].[p/Start_Period] TO [d/Version].[p/End_Period]

INTEGER @Month
@Month = MONTH([d/Version].[p/Start_Period]) + 12

IF [d/Date] = [d/Version].[p/Start_Period] THEN
	DATA([d/Account] = "Stat_PYAvgBal_AP") = (RESULTLOOKUP([d/Account] = "Stat_AP", [d/Date] = PREYEARLAST([d/Date])) +
											  RESULTLOOKUP([d/Account] = "Stat_AP", [d/Date] = PREVIOUS(@Month, "MONTH", [d/Date]))) / 2
ENDIF

If you want to calculate for all Period of Plan version, 

• Start_Period: 202501
• End_Period: 202512

MEMBERSET [d/Date] = [d/Version].[p/Start_Period] TO [d/Version].[p/End_Period]

INTEGER @Month

FOREACH [d/Date]
	@Month = MONTH([d/Date]) + 12

	DATA([d/Account] = "Stat_PYAvgBal_AP") = (RESULTLOOKUP([d/Account] = "Stat_AP", [d/Date] = PREYEARLAST([d/Date])) +
											  RESULTLOOKUP([d/Account] = "Stat_AP", [d/Date] = PREVIOUS(@Month, "MONTH", [d/Date]))) / 2
ENDFOR

I hope this is helpful to you, and if I have misunderstood anything, please feel free to reach out.

Moonjun

[RameshBandi]

Hi Moonjun,

Thanks for your help, let me try and get back to you with the results. 

Regards,

Ramesh