cumulative delta calculation using data actions in SAC

[guvvala_sairam]

We have a requirement to calculate the cumulative delta across the planning horizon(example from Jan 2025 to Dec 2026) ,we have 3 specific measures namely

1. IR_Shift ,2.CP_Rates ,3. Refreshed_rates

Ir_shift is the data action calculated measure where as cp_rates and refreshed_rates are input measure.

For the initial month of the planning horizon IR Shift is the difference of refreshed rates and cp rates(ir shift =refreshed - cp) progressively for  the  next month should be refreshed and cp difference minus previous month  ir shift and for the 3rd month should be refreshed and cp difference minus  sum of previous  2 month  ir shift.

In the below table we have values from Jan to Dec and the formulas will be

for 1st month ir_shift=refrehsed-cp  (1000-50)

for 2nd month ir =(2000-100)-950

similarly for 12th month ir = (refreshed - cp) - sum( all the 11 months ir_shift)

Measures	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
IR_Shift	950	950	1020	860	1080	1110	940	1050	990	1020	1020	800
CP_Rates	50	100	80	220	140	30	90	40	50	30	10	210
Refreshed_Rates	1000	2000	3000	4000	5000	6000	7000	8000	9000	10000	11000	12000

seeking Help for the data action script

[William_Yu · Accepted solution]

I would imagine similar code like this(I haven't tested it at all): 

MEMBERSET [d/Date] = BASEMEMBER([d/Date], "[2025]")
MEMBERSET [d/Measures] = "IR_Shift"

VARIABLEMEMBER #YTD_SHIFT OF [d/Measures]

FOREACH [d/Date] ASC
  DATA() = RESULTLOOKUP([d/Measures] = "Refreshed_rates") - RESULTLOOKUP([d/Measures] = "CP_Rates")
         - RESULTLOOKUP([d/Measures] = #YTD_SHIFT, PREVIOUS(1, "MONTH", [d/Date]))
  DATA([d/Measures] = #YTD_SHIFT) = RESULTLOOKUP() + RESULTLOOKUP([d/Measures] = #YTD_SHIFT, PREVIOUS(1, "MONTH", [d/Date])
ENDFOR

[MoonJun]

Hi @guvvala_sairam 

Please refer to another resolution of the Advanced formulas script.  

MEMBERSET [d/Date] = BASEMEMBER([d/Date], "[2025]")

VARIABLEMEMBER #Delta_CP_Rates OF [d/Measures]
VARIABLEMEMBER #Delta_Refreshed_Rates OF [d/Measures]

DATA([d/Measures] = #Delta_CP_Rates)
= RESULTLOOKUP([d/Measures] = "CP_Rates")
- RESULTLOOKUP([d/Measures] = "CP_Rates", [d/Date] = PREVIOUS(1))
DATA([d/Measures] = #Delta_Refreshed_Rates)
= RESULTLOOKUP([d/Measures] = "Refreshed_Rates")
- RESULTLOOKUP([d/Measures] = "Refreshed_Rates", [d/Date] = PREVIOUS(1))

DATA([d/Measures] = "IR_Shift")
= RESULTLOOKUP([d/Measures] = #Delta_Refreshed_Rates)
- RESULTLOOKUP([d/Measures] = #Delta_CP_Rates)

Regards,

Moonjun