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!