How to look up prior year values

Amankrydv
Amankrydv
edited October 2023 in Modeling

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.

Best Answer

  • Konstantin97
    Konstantin97
    edited August 2023 Answer ✓

    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

Answers

  • anirudh
    anirudh
    edited August 2023

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

Categories