How do I use a line item formatted by a month for mapping in a column dimensionalized by time

wld002
wld002
edited January 5 in Modeling
image.png

I have a value (in this instance it is the number 13 in January 24) that I need to populate in the line item below it in the month of February 25. The entire module is dimensionalized by time (month). The Feb 25 formula is an add months function that references an input from a different module based on the quarter of the current period. The sum function doesn't work at all and when I try the lookup it's doing the opposite of what I want (In this example it is using the Feb 25 date for mapping and populating the Jan 24 column with the values from Feb 25, I need it to do the opposite.)

Tagged:

Best Answers

  • pyrypeura
    pyrypeura
    Answer ✓

    I think you could use POST function. When using POST it you can determine value in the source month instead of target month.

  • rob_marshall
    rob_marshall
    Answer ✓

    @wld002

    Have you tried using the Post() function?

    image.png

Answers

  • Dikshant
    Dikshant

    @wld002,

    I tried a solution using OFFSET function. The source line item has similar values that I can see in your model, and Jan 24 has 13. Final Line item uses offset function. Offset Line Item is an manual input, which you can apply a formula/ condition to it based on your requirement. Like for Feb 25 you want to fetch values from Jan 24, that's 13 months backwards so I entered -13 in Offset Month line item for Feb 25. For Jan 25. I entered offset amount as 999, or any number greater than 24 will give you a zero, in case you don't want to fetch any values for a particular month.

    image.png

    Try it out and see if lemon is worth the squeeze 😊

  • wld002
    wld002

    So the offset and lag functions won't work because the months I need to bring forward are based on the Jan 24 date. In my example, if a lease starts in January 2024, the lease term is entered in a different module and can be changed. So if it's 12 month lease term then I would now need the 13 to go into Jan 2025. I want to automate this function. My thought is to turn off the time scale of the forecast month that I need to flow into the actual period.

Categories