Forum Discussion

jeraldine's avatar
jeraldine
Frequent Visitor
5 years ago

Forecast future months using average

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
jeraldine
Frequent Visitor
5 years ago

Hi Anonymous ,

 

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!