Future year to grab actuals data

User2021 · 2025-06-03T13:59:31+00:00

There was an error rendering this rich post.

User2021

Can someone please help me with a formula that grabs data from a prior period (actual) for future forecast months and years?

For example, I want 2025, 2026 and 2027 to be the same data as 2024. In my example, the switch over is currently on Feb 2025. Jan 2025 is actual but in Jan 2026 and Jan 2027, I want to use what is in Jan 2024, not Jan 2025.

					switch over is Feb 2025
	Actuals	Actuals	Actuals	Actuals	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast
	Jan 2024	Feb 2024	Mar 2024	Jan 2025	Feb 2025	Mar 2025	Jan 2026	Feb 2026	Mar 2026	Jan 2027	Feb 2027	Mar 2027
Data	555	666	777	12345	666	777	555	666	777	555	666	777

The formula I used is a LAG (see below) but Jan 25 is an actual month right now with the lag formula, Jan 26 and Jan 27 is pulling Jan 25 data. However, I want Jan 26 and Jan 27 to pull data from Jan 24 instead of Jan 25. And next month Feb 2025 will be an actuals month, and so i want Feb 26 and Feb 27 to pull Feb 2024 data and so on and so forth as switchover changes.

I imagine I need to add a Lookup to Data in the LAG formula but I don't know how to create the lookup to make it pull the prior year's actuals in future years. Can someone please help?

ISACTUALVERSION()

THEN

DATA

ELSE

LAG(DATA, 12, 0)

Thank you!

Accepted answers

All comments

gaurav_sharma-bng

Hi @n0200309 - Try using MOVINGSUM(line item -12, -12) - It is also better performance wise compare to LAG. Screenshots below -

Source module -

Target module -

Regards,

Garry

User2021

Hi Garry_BH, The problem is Jan 2026 and Jan 2027. It is pulling Jan 2025 value but I want Jan 2024 value instead. Right now, Jan 2025 is actuals and Feb 2025 is forecast. I want Jan 2026 and Jan 2027 to pull Jan 2024 value. How can I do that? I might need a sys module and lookup but I don't know what formula will work.

Thank you!

andrewtye

Try having the 12 as a line item. Does make the line item less performant but can then gain the flexibility.

devrathahuja

Hi @n0200309 ,
You can modify the formula to create a loop & use - If first year then lag(data,12,0) else lag(final line item,12,0)

The FY25 is an example here, you can set it up as a dynamic boolean to identify the first year after your full completed year - FY24 in your case. So when you move to next year, it will use the FY25 values & put it to FY26 & FY27.

Hope this helps! :)

gaurav_sharma-bng

Hi @n0200309 ,

The build for this would be as below -

1) Determine actual periods

2) Determine the corresponding month in the anchor year (FY24 in your case) for all other time periods.

Below is the build for the same -

a. Data module (with version) -

b. Time module to determine month mapping and actuals -

c. Summary module with lookup (also better than lag performance wise)

Regards,

Garry

Quick Links

Can you help?

Unanswered questions

Regarding WSA and NON WSA access
Hi, I have a model where the Channel changes based on the input date given by user, we have a Unique key based on the person and the Channel, suppose if the user gives his mail id, change the channel along with date in UX pages and after running the process approve the details of the previous record becomes un-editable and…
Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Not Able to Join the Level 1 Model Building Course
Hey Everyone, I was trying to learn about the Level 1 Model Building Course, when i log in to my system and click on "Start the course" option its showing training unavailable, Can anyone please help me with this @Dikshant and all?