Calculating Forecast using sum of previous year actuals and input of current month forecast

[Insan28]

Hi All,

I have a requirement where i need to calculate the forecast using below calculation.

Fiscal Yr = Oct to Sep 

We are currently in 202407 

then calculate SUM of Actuals of 2023 of account A / SUM of Actuals of 2023 of account X * Forecast value entered for the respective period on account Y

Forecast months of Account A= Sum of Actuals_Last_Year of Account A / Sum of Actuals_Last_Year of Account X * Forecast input of account Y of current month on which Forecast is being calculated.

I am trying to achieve this using data action as account dimension formula is not able to fulfill this requirement.

I am using prompt for getting the start of the forecast period so that the formula can be applied from there on.

Please can someone suggest how I can aggregate last year actuals of account A and X because I am not able to set the MEMBERSET for DATE dimension as actuals should be taken from last year and the write back should happen on the months starting from the Forecast months 

Please share your thoughts if anyone has implemented similar requirement.

Please let me know if any clarification is needed 

Thanks in advance.

Regards,

Sana 

[N1kh1l]

@Insan28 

It will be easier to help if you create an example in excel and show the logic clearly of what you intend to calculate. You will have to use PREVIOUS() to look back. Again easier to understand if an example was provided.

Nikhil

[Insan28]

Yes Sure adding example below

 

so in order to calculate the FC values , I need to calculate sum of last year actuals of 62xxxxxx and FTE Calculated within data action and then multiply it with values entered on current FC months. Apart from this,  I have to additionally provide start period of FC months and end period of FC.

Please let me know incase of any clarification.

Regards,

Sana