Forecasting future months using YTD Average

[Martij03]

Hi there

I was wondering if you can shed some ligth on the following issue.

I have been building the table below using DAX Measures, but my FORECAST measure is not running properly.

02. Year-Month (Numeric)	MONTHLY ACTUALS	ACTUALS RUNNING TOTAL	ACTUALS MONTHLY AVERAGE	YTD ACTUAL MONTHLY AVERAGE	FORECAST	PROJECTED ACTUALS	PROJECTED ACTUALS RUNNING TOTAL
2021-07	2,488,049.21	2,488,049.21	2,488,049.21	2,488,049.21		2,488,049.21	2,488,049.21
2021-08	2,657,616.36	5,145,665.57	2,657,616.36	2,572,832.79		2,657,616.36	5,145,665.57
2021-09	2,471,660.84	7,617,326.41	2,471,660.84	2,539,108.80		2,471,660.84	7,617,326.41
2021-10	3,289.95	7,620,616.36	3,289.95	1,905,154.09	2,539,108.80	2,542,398.75	10,159,725.16
2021-11	-	7,620,616.36	-	1,524,123.27	2,539,108.80	2,539,108.80	10,159,725.16
2021-12	-	7,620,616.36	-	1,270,102.73	2,539,108.80	2,539,108.80	10,159,725.16
2022-01	-	7,620,616.36	-	1,088,659.48	2,539,108.80	2,539,108.80	10,159,725.16
2022-02	-	7,620,616.36	-	952,577.05	2,539,108.80	2,539,108.80	10,159,725.16
2022-03	-	7,620,616.36	-	846,735.15	2,539,108.80	2,539,108.80	10,159,725.16
2022-04	-	7,620,616.36	-	762,061.64	2,539,108.80	2,539,108.80	10,159,725.16
2022-05	-	7,620,616.36		692,783.31	2,539,108.80	2,539,108.80	10,159,725.16
2022-06	-	7,620,616.36	-	635,051.36	2,539,108.80	2,539,108.80	10,159,725.16
Totals	7,620,616.36	7,620,616.36	635,051.36	635,051.36	2,539,108.80	10,159,725.16	10,159,725.16

I would like to use the previous month YTD Monthly Average as the projected value for the following months and calculate a running total to come up with the FY Result.

The table is showing the forecast values in the correct months, but it's not showing the correct total per column.

Logic tells me that I have calculated the Forecast value for one month and that needs to be replicated to the subsequest month, but unsure how to do that.  

I feel that I'm missing something, but I'm teaching myself to use Power BI and to be honest I don't know where to start. 

I have used the following formulas:

FORECAST =

CALCULATE(

CALCULATE( [ACTUALS MONTHLY AVERAGE],

FILTER( ALL( 'DATES'),

'DATES'[02. Offset - CurMonth] <= -1 &&

'DATES'[05. Financial Year] = MAX( 'DATES'[05. Financial Year] ) ) ), 'DATES'[02. Offset - CurMonth] >= 0 )

******

PROJECTED ACTUALS = [MONTHLY ACTUALS] + [FORECAST]

******

PROJECTED ACTUALS RUNNING TOTAL =

CALCULATE(

    [PROJECTED ACTUALS],

    FILTER(

        ALLSELECTED('DATES'[Date]),

        ISONORAFTER('DATES'[Date], MAX('DATES'[Date]), DESC)

    )

)

******

I hope you can put me in the right direction.

I look forward to hearing from you.

Cheers

Jose

[Icey · Accepted solution]

Hi @Martij03 ,

Try this:

FORECAST =
IF (
    ISBLANK ( SUM ( 'Fact Table'[MONTHLY ACTUALS] ) ),
    CALCULATE (
        [YTD ACTUAL MONTHLY AVERAGE],
        DATES[YearMonth] < MAX ( DATES[YearMonth] )
    )
)

Best Regards,

Icey

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

[Icey · Accepted solution]

Hi @Martij03 ,

Try this:

FORECAST =
IF (
    ISBLANK ( SUM ( 'Fact Table'[MONTHLY ACTUALS] ) ),
    CALCULATE (
        [YTD ACTUAL MONTHLY AVERAGE],
        DATES[YearMonth] < MAX ( DATES[YearMonth] )
    )
)

Best Regards,

Icey

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

[Martij03 · Accepted solution]

Thank you Icey.

I changed my approach and did the calculation a bit different and now is working.

Here is the code I used to arrive to the EOFY Expected Result based on YTD Actual Monthly Average:

FULL YEAR EXPECTED OUTCOME = 
VAR MonthstoEOFY = 
CALCULATE( MEDIAN ( DATES[02. FY Periods Remaining] ) ,
    KEEPFILTERS ( VALUES ( 'DATES'[Date] ) ) ) 

RETURN 
CALCULATE([YTD ACTUAL] + ([YTD ACTUAL MONTHLY AVERAGE] * MonthstoEOFY),
       'DATES'[02. Offset - CurMonth] < 0 )

I created a remaining months column in my Dates table to help me with this measure.

A bit of rounded way, but it is giving me the results I need. So much to learn 😅  

Thank you so much for your help.

Cheers

Jose M