Forecast Data using TIMESUM function

Jagmit
Contributor

Forecast Data using TIMESUM function

Hi Team,

 

I have one situation, where I am calculating a Line-item in Forecast scenario using Sum of Last 12 months (from the Current Period). Need your inputs to help with the below model design.

Scenario 1:

Current period = Jan 2021

Actual Version = Jan 2020 till Dec 2020

Forecast Version = Jan 2021 till Dec 2021 (i.e. including current period)

 

Forecast_LI = TIMESUM(ACTUAL_LI, -12,-1) i.e. Sum of Actual Values from Jan-Dec '20 for all periods in Forecast Scenario as the Formula scope is Forecast.

 

Scenario 2: (when we change Current Period to Feb 2021)

Current period = Feb 2021

Actual Version = Jan 2020 till Jan 2021

Forecast Version = Feb 2021 till Dec 2021 (i.e. including current period)

 

Forecast_LI = TIMESUM(ACTUAL_LI, -12,-1) i.e. Sum of Actual Values from Feb'20 - Jan '21 for all periods in Forecast Scenario as the Formula scope is Forecast.

 

But what I want is:

For Jan 2021 Forecast Data it should still be:  Sum of Actual - Jan'20 till Dec'20 from Scenario 1.

and Feb 2021 - Dec 2021 (i.e. 2nd Scenario), it will be : Sum of Actual - Feb'20 till Jan'21.

 

Kindly advise.. Thanks !

 

 @rob_marshall @DavidSmith @Misbah  

 

 

Regards,

Jagmit

 

 

14 REPLIES 14
nathan_rudman
Community Boss

Re: Forecast Data using TIMESUM function

I'm not very clear, but I'd say you want to use LAG(actual,12,0)

 

https://help.anaplan.com/en/b10966f6-334f-4eb4-8c24-9a31f6c785ba-LAG


Nathan Rudman, Anaplan Model Builder
Jagmit
Contributor

Re: Forecast Data using TIMESUM function

Hi Nathan,

Thanks for the quick revert.

No, LAG won't work here as I am looking for Sum of Last 12 periods with respect to the Current Month.

But if we change the Current Month i.e. shift from Jan'21 to Feb'21, my Forecast Line Item will calculate new values and will over-write previous Forecast values when Current Month was Jan'21 for the Jan'21 period.
So, in brief:
Current Year Months = Jan'21 till Dec'21: The Forecast values shouldn't change for (historical current year months)
Misbah
Master Anaplanner/Community Boss

Re: Forecast Data using TIMESUM function

@Jagmit 

 

Why don't you use MOVINGSUM. If you have to sum the values for last 12 months based on the month you are in, it should be able to do it. Unless I am missing something.

 

TIMESUM should be used when you don't have Time as a dimension in your target module. It seems you have Time in the target module

Jagmit
Contributor

Re: Forecast Data using TIMESUM function

Hi Misbah,

 

Thanks for the inputs.

 

I am attaching an Excel here, maybe that will help explain the Problem statement.

 

I can't use MOVINGSUM here, as the Forecast Month values i.e. (Current Month and onwards) the values need to be same.

 

 

 

 

MarkWarren
Expert

Re: Forecast Data using TIMESUM function

I'm not entirely sure I understand what's needed.
Here's what I have interpreted it as:
image.png
So we do a different TIMESUM for each period, unless you don't have data 12 months in advance of that in which case it shows the previous value.
Kind of matches your spreadsheet...

Jagmit
Contributor

Re: Forecast Data using TIMESUM function

This is close, only thing different here is the Sales_calculated line-item to be populated Jan 21 onwards.(i.e. Current Year Months)
MarkWarren
Expert

Re: Forecast Data using TIMESUM function

You could add a Timerange with the extra year in the future (FY22) and this would work. Hide that future year on the grid.
This allows you to do the ITEM(Time) + 12 for Current Period (or set manually) for all of FY21

Misbah
Master Anaplanner/Community Boss

Re: Forecast Data using TIMESUM function

@Jagmit 

 

You can use @MarkWarren solution and use OFFSET in another line item to push the values to 2021

Jagmit
Contributor

Re: Forecast Data using TIMESUM function

@MarkWarren @ChrisHeathcote @Misbah 

 

Thank you for your inputs. I am attaching the solution we have decided to go for 'Monthly' update of Current Period.

I would only need End Period for lookup and also using MOVINGSUM and 1 Calculated LineItem, as everyone suggested to go for it when it was all figured out.

 

Also,

I am trying to figure out a solution for Rolling Forecast 'Quarterly' update of Current Period, to have the flexibility to choose the frequency of Current Period update. (Please see attached Excel) 

Please let me know your inputs.

 

Thank you!

 

Regards,

Jagmit