Driver Calc help with logic

Walker32

I have a driver called "set to plan" which is just assigning the planned amounts to the forecast periods, but this is only happening for 2022 forecast periods.

 

The previous driver Calc:

IF
    Driver Method <> 'SYS002 - Model Lookups'.Set to Plan
THEN
    0
ELSE
    'REP001 - Balance Sheet (Reporting Output)'.Amount[LOOKUP: 'SYS006 - Version Management'.Associated Plan]

 

Ideally what I want the logic to do is if the Current Year > than Year of the Switchover Period (May22) then use the PY period's value. 

 

To start I added a Switchover Period(Year) line item to the sys006 module so i can refer to it. and this is the change I made to the formula:

IF
	Driver Method <> 'SYS002 - Model Lookups'.Set to Plan
THEN
	0
ELSE
	IF
		'SYS006 - Version Management'.Current Year > 'SYS006 - Version Management'.'Switchover Period (Year)'
	THEN
		LAG('Total Output ($) [Staging]', 12, 0)
	ELSE
		'REP001 - Balance Sheet (Reporting Output)'.Amount[LOOKUP: 'SYS006 - Version Management'.Associated Plan]

 

however the values for FY23 are still showing up as 0. What am I missing?

 

Attached the screenshots of the Modules CAL001 Periods (Feb22, May22, Feb23) and SYS006(blueprint and view) for reference.

SYS006_140923.jpg

Screenshot_2022-06-22_225554_140923.jpg

SYS006_Blueprint_view_140923.jpg

JensitSebastian

Hi @Walker32 ,

 

For the updated formula once you have filled in 'SYS006 - Version Management'.'Switchover Period (Year)'= PARENT('SYS006 - Version Management'.'Switchover Period (Month)); you would get FY23. 

 

Ideally I would have compared these with a SYS Time module items. Like build a "SYS Time" module with Time as dimension. Time Rage can be Model Calendar or Otherwise and Time Scale Month. 

Then you can identify each month with SYS Time.Month=Period(START()) formula.

Use a Boolean to check if the Period is greater than the switchover month. If greater then pick the previous year same month value. For that  formula will be SYS Time.PY Same Month =  SYS Time.Month-12. And use SYS Time.PY Same Month lookup in the formula.

 

Thanks,

Jensit