Formula - Previous?

CommunityMember113484
Occasional Contributor

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
ChrisAHeathcote
Community Boss

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

 

Chris
HeathcoteAndHerran.com

View solution in original post

ChrisAHeathcote
Community Boss

@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

 

Chris
HeathcoteAndHerran.com

View solution in original post

5 REPLIES 5
ChrisAHeathcote
Community Boss

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

 

Chris
HeathcoteAndHerran.com
CommunityMember113484
Occasional Contributor

Thanks Chris - I've created the new module - but the formula doesn't seem to work for me?
i've got format as year, time scall and timerange not applicable?
ChrisAHeathcote
Community Boss

@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

 

Chris
HeathcoteAndHerran.com
ChrisAHeathcote
Community Boss

If your target module contains a time dimension then you lookup module needs to contain the same dimensions. 

If this is by month then create the same lookup module by month and use the following formula to populate a cell containing the previous years for each month of the model calendar.

= PERIOD(END())-1

 

Chris
HeathcoteAndHerran.com
Kristina_Mihay
Contributor

Hello,

 

 

check the following link where a similar case is discussed in details 🙂 https://community.anaplan.com/t5/Anaplan-Platform/How-to-get-the-previous-period-of-current-period/t...

 

Best regards

Kristina