Subtracting Months when using lookup

[C0000]

Hello..  I'm trying to figure out how to write a lookup formula that will subtract months.

For example, I have the following line items

Hire Date (Month format)  Set for Jun 19

Revenue (Number format) Has 100 for Jan and each month increases by 100 (so 200 for Feb, 300 for Mar, etc)

Test Result (Number Format) 

In Test Result, I'm trying to create a formula like:  Revenue[Lookup: Hire Date] - 1 (Thus subtract 1 month from Hire date, so instead of June, I would get May's revenue number), but the different formula combinations I have tried, doesn't seem to work

Revenue[Lookup Hire Date - 1] (didn't like the formula)

Revenue[Lookup 'Hire Date' -1] (didn't like the formula)

Revenue[Lookup Hire Date] - 1. (liked the formula, but got back 599, which wasn't correct)

Thanks!!

[Misbah]

Hi,

If there is a time dimension it can be done in multiple ways

Try Using the formula: 

Revenue - OFFSET(Revenue,-1,0)

Revenue - PREVIOUS(Revenue)

Thanks,

Misbah

[C0000]

When I tried either of these formula's, the result wasn't the expected answer

[Misbah]

Can you please share a screenshot What exactly are you trying to achieve. I might have misunderstood your requirement.

[C0000]

Under Test Formula, I'm trying to write a formula like:

Test Formula:   Revenue[Lookup Hire Date] - 1 (- 1 meaning subtract one month), which would populate May's number since May one month prior to the Hire Date of June

[Misbah]

@C0000 

You may have to use IF ELSE function here. Can you use the below formula and see if this works

IF ITEM(Time) >= Hire Date THEN Revenue - PREVIOUS(Revenue) ELSE 0

Hope this helps!

Misbah

[jnoone]

Hi,  you can create the following line items:

1.  "hire date" - manual entry (can be linked to non-time dimensionalized module)

2.  "month lookup" - formula is "period(hire date) - 1" with format of "month". 

3. "revenue" - manual entry

4.  "test result" - formula is "Revenue[LOOKUP: Month Lookup]"

This will give you May value in the month of June.  Screenshot also attached.  I think this should accomplish what you need or get you close but let me know if not the case.  Also looks like revenue is the only line item which is changing over time so you can change the applies to, to not applicable for the rest.  

-Jt

[DavidSmith]

Better still to have those subsidiary views in a separate module

[Pooja]

Hello,

For this scenario, create separate line time to get Hire Date-1as shown below then use it in Lookup clause.

[C0000]

Hi Pooja... Your mentioning of adding additional line items and subtracting # from hire date and doing the lookup with the new line item works (jnoone also mentioned the same thing).  Except the module I'm working with is very large and was trying to see if I could cut down the number of cells by adding what the "Hire Date - 1" line item were doing within the formula on the "Test" line item in your example.

A few minutes ago, I created another module like in the picture below.  My thinking was seeing if could Match Hire Date to Mnth and bring back the specified "Month - #" that I wanted to use, but not sure if that would work either