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)
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.
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