Forecast future months using average

[jeraldine]

Hi,

I would really appreciate if someone can help with a measure I've been trying to figure out for a few days now. My DAX is not good so maybe this would be easy for someone.

What I'm trying to do is come up with a 12-month forecast based on average month to month for the month being forecasted multiplied by the previous month. I already have the month to month average. I followed this blog post on recursive calculations, but cannot get the output I'm looking for. I think my measure is correct up to the Multiply by measure.

This is an excerpt from my data. The last value is February. For March it should multiply February value to the March month over month average which is 105.93%. For April, take the March Forecast and multiple to April month over month average and so on. Another problem with the database is all the dates are showing in the table. I only want to see dates in slicer.

I posted a sample of my pbix here.

Thanks in advance!

[v-stephen-msft]

Hi @jeraldine ,

Is your measure QuoteValue MoM% the percentage of this month divided by the previous month? If yes, please see the attachment.

QuoteValue MoM% =
VAR __PREV_MONTH =
    CALCULATE (
        SUM ( 'TitanQuote'[QuoteValue] ),
        PREVIOUSMONTH ( 'DateTable'[Date] )
    )
RETURN
    DIVIDE ( SUM ( 'TitanQuote'[QuoteValue] ), __PREV_MONTH )

Forecast =
VAR _previousmonth =
    CALCULATE (
        SUM ( 'TitanQuote'[QuoteValue] ),
        PREVIOUSMONTH ( DateTable[Date] )
    )
VAR _lastyear =
    CALCULATE (
        [QuoteValue MoM%],
        FILTER (
            ALL ( 'DateTable' ),
            YEAR ( [Date] )
                = YEAR ( MAX ( 'DateTable'[Date] ) ) - 1
                && MONTH ( [Date] ) = MONTH ( MAX ( 'DateTable'[Date] ) )
        )
    )
RETURN
    _previousmonth * _lastyear

Best Regards,

Stephen Tao

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

[jeraldine]

Hi @v-stephen-msft ,

You formula worked to show 1 month forecast, but I need to show 12-month future forecast. Also, the last value should be multiplied by the Value MoM% Ave, which is an average of the month over month for the same months over the years in the slicer. Value MoM% Ave is a measure I already have.

Just an update, I already figured out how to show only dates in slicer, but the forecast value is still incorrect. Here is the formula I'm working with:

Forecast = 
VAR currentmonth =
MAX ( DateTable[Date].[Month] )
VAR newtable =
FILTER (
ADDCOLUMNS (
SUMMARIZE ( ALL ( DateTable ), DateTable[Date].[year], DateTable[Date].[Month] ),
"@momratio", [QuoteValue MoM%]
),
DateTable[Date].[Month] = currentmonth
)
var dateRange = DATESBETWEEN('DateTable'[Date], min(DateTable[Date].[Date]),max(DateTable[Date].[Date]))
var MoM_Ave=
AVERAGEX ( newtable, [@momratio] )
var multiplyby=if(ISBLANK([Last Quote]), MoM_Ave, [Last Quote])
VAR newtable2 =
ADDCOLUMNS (DateTable,"month",DateTable[Date].[Month])
return
if(ISBLANK([QuoteValueTotal]), calculate(PRODUCTX(newtable2,multiplyby), DATESBETWEEN(DateTable[Date], BLANK(),max(DateTable[Date]))),[QuoteValueTotal])

Here's the value that I'm getting:

The correct forecast for March should be 122,985,850.62 * 105.93%. For April, it should take the forecasted value for March and multiply it by 99.72%.

Any help on this would be greatly appreciated.

Thanks!