Lookup between Time Periods and Text

Highlighted
New Contributor

Lookup between Time Periods and Text

I'm reaching out for guidance on how to build a link between Full-Year time periods and a list-equivalent Fake Years. i.e.: FY19 = "Year One", FY20 = "Year Two", etc.

 

Some background, I have a P&L line item "Revenue" that has a year ending balance of $100,000 in 2019 and $150,000 in 2020. These revenue amounts begin to amortize following the year it is listed. I need to amortize the FY19 $100,000 on my defined % pattern based on annual rates: (Year One "2019" = 20% during 2020, 30% during 2021, 35% during 2022, et al.). FY20's $150,000 will follow the same % pattern starting in 2021, but I need to keep it separate in "Year Two".  My end result is a module which, for Fiscal Year 2021, will sum 2019's 2nd year Pattern (30%) and 2020's 1st year Pattern (20%). 

 

I have a module which converts the FY Time Scales to a text version of my Fake Years "Year One, Year Two, Year Three..." through the "FINDITEM" formula. Then a second module shows my full time scale (by months) against my List of Fake Years which will apply the % Pattern each month. I'm struggling with using a Lookup formula to populate "Year One" column with FY20's Revenue amount ($100K). 

 

How can I bring the 2019 $100,000 into the "Year One" column in this second module? Lookups between text and time periods has been my most challenging area to understand coming from a strong Excel background.

2 REPLIES 2
Highlighted
Certified Master Anaplanner

i Re: Lookup between Time Periods and Text

Hi @rhightower ,

 

I think your case is similar to this one

 

https://community.anaplan.com/t5/Anaplan-Platform/Calculation-Inquiry/td-p/74507

 

Regards,

Andre

Highlighted
Community Boss

Re: Lookup between Time Periods and Text

See if this helps..

 

ArunManickam_5-1595901405593.png

 

 

ArunManickam_0-1595901271703.png

 

ArunManickam_1-1595901297831.pngArunManickam_2-1595901312807.png

 

ArunManickam_3-1595901343296.png

 

ArunManickam_4-1595901368799.png

Thanks

Arun