Calculating future months using latest actuals based on expected growth

[mixue100]

Hi 

I am stuck with this and would need help on the below. 

Month	YTD Actuals	Expected growth for next mont, based on actuals from Current month	Expected actuals (forecasted actuals)	Actuals or Forecast	Source
Jan-22	500	5%	525	500	Actuals
Feb-22	1,000	2%	1,020	1,000	Actuals
Mar-22	1,200	6%	1,272	1,200	Actuals
Apr-22	600	10%	660	600	Actuals
May-22	800	0%	800	800	Actuals
Jun-22	900	-5%	855	900	Actuals
Jul-22	200	-3%	194	200	Actuals
Aug-22	100	10%	110	100	Actuals
Sep-22	300	0%	300	300	Actuals
Oct-22	Not available yet	-5%	285	285	Forecast
Nov-22	Not available yet	6%	302	302	forecast
Dec-22	Not available yet	10%	332	332	forecast
Jan-23	Not available yet	0%	332	332	Forecast

 I have actuals until September. I would to have a measure that helps me to derive at the column in blue font - that is having YTD actuals + forecasted. 

E.g. October's expected figure (blue) is based on September's actuals + growth of -5 %

i.e. actuals of 300 from september minus 5 % = 285 

November's expected figures (blue) is based on october's expected figures + growth of +6%

expected actuals of october (285) plus growth of 6% gives me expected november figures of 302. 

Appreciate your help! 

[mangaus1111 · Accepted solution]

Hi @mixue100 ,

this is the measure for the forecasts. Please hit the thumbs up & mark it as a solution if it helps you. Thanks.

Forecasts =

VAR LastActualMonth =

            CALCULATE (

                MAX ( 'Table'[Month] ),

                ALL ( 'Table' ),

                'Table'[YTD Actuals] <> BLANK ()

            )

VAR LastActual =

CALCULATE (

                SUM ( 'Table'[YTD Actuals] ),

                ALL ( 'Table' ),

                'Table'[Month] = LastActualMonth

            )

VAR CurrentMonth = MAX('Table'[Month])

VAR Product_Growth =

CALCULATE(

 PRODUCTX(

        FILTER('Table',

               'Table'[YTD Actuals]=BLANK() && 'Table'[Month] <= CurrentMonth

              ),

        'Table'[Expected growth] + 1

    ),

    ALL()

)

RETURN

Product_Growth * LastActual

And here there is the link to the pbi file:

https://1drv.ms/u/s!Aj45jbu0mDVJiGdkR76ccaoHdFht?e=rAmtHb

[v-jingzhang]

Hi @mixue100 

If the expected growth rate is a fixed value, this is possible. You can try the following measure. For example, the growth rate is 10%. 

If the growth rate is not fixed, just like your sample, I cannot think of a solution yet. The difficulty is at the highlighted _forecast part. The difficulty is that in DAX, the measure is evaluated on every row individually. It cannot get the measure value from its previous row. 

Best Regards,
Community Support Team _ Jing