Project forward YTD average and rolling average

[Dandeats]

Hi this is my first post 🙂

I'm working on a report where I give the users multiple forward scenarios based on costs incurred historically. They can then compare the values vs. their own forecast.

I have struggled with something for about 2 weeks now, so am reaching out for help! What I would like to do is create 2 scenarios which consider run-rates and then use the calculated rates to project forward into the remaining months of the year (that haven't been actualised). I did the mock up in excel to illustrate what I mean.

For other scenarios I have been more successful, for instance I used this one for a scenario which takes YTD actuals + Last Year's spend.

Fcst (AC + Last Yr) = 
VAR LastDateWithActuals = [LastActualDate]

VAR CostFlex = 1 + [Forecast_Flex Value]

VAR Result = 
    CALCULATE(
        [AC (1 Yr Ago)] ,
        KEEPFILTERS ('Time'[DATE SQL] > LastDateWithActuals )
    ) * CostFlex
    
RETURN

CALCULATE([Actuals (AC)] + Result)

I have then been subsetting out the measure [AC (1 Yr Ago)] for other similar measures to give me my scenario, but this wont work for the YTD average and Rolling average ... as I couldnt seem to link the forward values in time, I ended up making a measure that when displayed on a card would give me the total value for the year, but for the report to work (with calc groups for MTD, QTD and YTD), I'd love to get it working as per the Excel snippet so that I can add to my matrix and column chart which has time in it.

Fcst (AC + RunRate) = 
VAR Costflex = 1 + [Forecast_Flex Value]
RETURN

[AC (YTD)] + ((12 - [Months lapsed]) * ([AC (YTD Avg)] * Costflex))

*************************************************************************

Fcst (AC + Roll RunRate) = 
VAR LastDateWithActuals = [LastActualDate]
    
VAR Periods = [Roll_Month Value] * -1

VAR Actuals = 
    CALCULATE([Actuals (AC)],
    DATESINPERIOD('Time'[DATE SQL],LastDateWithActuals,Periods,MONTH)
    )

VAR MthLapsed = [Months lapsed]

VAR Costflex = 1 + [Forecast_Flex Value]

RETURN

[AC (YTD)] + ((12 - MthLapsed) * DIVIDE(Actuals,(Periods*-1),0) * Costflex)

Hoping you can help, please shout if i need to clarify anything!

Thanks!

[Dandeats]

Hi just to follow up - I prepared a simpler PBIX file which hopefully you can access from here ... 

What I would like to do is to fill in the remaining months for scenario 3 and 4 by projecting forward / using the YTD avg and rolling avg respectively ... the desired output would look something like this ... and the series be "summable" ...

Hoping someone can help 🙂 Thank you

[v-lionel-msft]

Hi @Dandeats ,

Do you mind providing a sample .pbix file?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.