nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to look up prior year values

Amankrydv

I want to lookup for previous year March values. For example if my current month is june FY24, then i want to lookup for March FY23 Values.. how can i make this lookup.

Accepted answers

Konstantin97

Hi @Amankrydv!

For such things you can update Anirudh's approach.

E.g. Create line items "Current year" with Year format and formula period(currentperiodstart())

Then instead of FINDITEM(Time, "Mar FY23") use FINDITEM(Time, "Mar " & NAME(Current year - 1))
It will give you March item for previous year

Make sure you change current period every year, since this approach uses it to identify current year

Thanks,
Konstantin

All comments

anirudh

Create a line item in a module with only time dimension and call it March FY23

Set the format to Time Period - Month and use the following formula

FINDITEM(Time, "Mar FY23")

Make sure the cell is populated. That depends on what exactly the time period is called in your model

Then in your module where you want to hardcode the number across all months, use the formula:

Data[LOOKUP: Time Module.'March FY23']

This is considered hardcoding so there might be better ways to get the same result but this should work for now, let me know if it does!

Amankrydv

Thanks Anirudh
but that didn't answer my question. I want March month of Previous Years. For example, if I am in FY24 then I need March of FY23, similarly if I am in FY23, I need lookup for March month values of FY22.

Konstantin97

Hi @Amankrydv!

For such things you can update Anirudh's approach.

E.g. Create line items "Current year" with Year format and formula period(currentperiodstart())

Then instead of FINDITEM(Time, "Mar FY23") use FINDITEM(Time, "Mar " & NAME(Current year - 1))
It will give you March item for previous year

Make sure you change current period every year, since this approach uses it to identify current year

Thanks,
Konstantin

cyprian_malak

For those seeking the most optimal solution, here it is:

Example pertains to Diminishing Value calculation, but it can be applied to any scenario requiring a lookup of past year values. In this case, we use the total last year diminishing value to reduce the opening balance for the next year.

Step by step:

Enable Time Summary on Diminishing Value
Create Diminishing Value PY line item with Year only and the following formula:
PREVIOUS(Diminishing Value)
Update the formula for the Opening balance to deduct on the 1st month of the fiscal or calendar year (January in this example):
IF Start? THEN Initial Opening Balance ELSE PREVIOUS(Opening Balance) - IF Jan? THEN YEARVALUE(Diminishing Value PY) ELSE 0

Quick Links

Can you help?

Unanswered questions

Integration Process Execution Access - Which Access Takes Precedence?
We have an Integration Process in Anaplan CloudWorks created by User X - No WSA (originally Full Access with top-level Selective Access, now changed to Planner Read Only with Selective Access unchanged) and scheduled by User Y - with WSA , who also created the Integration Flow. Internal (Full Access) is enabled within the…
Anaplan ⇄ Google Sheets pivot tool — feedback welcome
We've been building a tool called aplan4sheets and we'd genuinely like input from this community — both on what we've built so far and on what we should prioritize next. The problem we set out to solve: Anaplan's Excel add-in is Windows-centric, and there's no real native way to pivot Anaplan data in Google Sheets. We…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…