Formula - Previous?

Hi,

I have a module that shows the full-year forecast amount for various expense categories.  

It also compares the current forecast (2021) to the prior forecast using the formula PREVIOUS(Full year forecast). That was fine in 2020, but now we are in 2021, the prior forecast shows nil. 

How can I fix this so it picks up the previous forecast (2020)? 

Thanks

Tagged:

Best Answers

  • @CommunityMember113484 

    Use a time lookup set up in a flat module.

    Create a time period formatted line item in a new module containing no dimensions. Set it to years. 

    Formula = PERIOD(CURRENTPERIODEND())-1

    This will default to the previous year of the current model calendar.

    Use a LOOKUP function against this line item to pull the previous years data. 

     

  • @CommunityMember113484 

    Firstly, have you set the CURRENT PERIOD in the model time settings?

    ChrisHeathcote_0-1603731778258.png

    Update this setting and the following formula should work correctly.

    =PERIOD(CURRENTPERIODEND()) - 1

    Place the -1 outside the brackets.

    ChrisHeathcote_1-1603731907577.png

     

    ChrisHeathcote_2-1603731939243.png

     

Answers