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

[N1kh1l]

@RameshBandi 

If you are worried about 12 month of data being queried, you can maintain an attribute in the version dimension for e.g START_DATE and maintain the CY jan as value so 202501. 

You can scope the date as MEMBERSET [d/Date]=[d/Version].[p/START_DATE] TO [d/Version].[p/START_DATE]

This will set the scope to 202501 only and only that records will be in scope for the 2 accounts. Now the below is only in reference to 202501 so no need for the IF statement to check for January.

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

FIRST/LAST/PREYEARLAST, None of them can be used in MEMBERSET. So only way I think using an attribute in Version dimension.

Hope this helps !!

Nikhil

[RameshBandi]

Hi Nikhil,

Thanks for your quick response, the issue actually to calculate previous year end balance + Prior Previous year end balance. Irrespective of month I am in current year. 

As mentioned we need to calculate : 

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

[N1kh1l]

Ramesh, and your code is exactly doing the same but your worry was you were querying unnecessary records. Setting the scope to 202501 will prevent that. Irrespective of which month you are in current year the carry forward will only happen to Jan from ( LY Dec +LY-1 Dec)/2. If I have misunderstood anything kindly clarify.