Forum Discussion

Tobz007's avatar
Tobz007
Frequent Visitor
1 year ago
Solved

How to create a Dynamic 15 Months Forecast

Hi, I'm trying to create a 15 months forecast with the sample data below for each client on a monthly basis, I'll also like to apply a reduction factor (percentage) to the forecast result 12 months i...
Show and Tell
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello Tobz007,

     

    Can you please try the following:

    Dynamic 15Month Forecast Revenue = 
VAR CurrentDate = MAX('DataTable'[Date])
VAR Last12MonthsAvgRevenue = 
    CALCULATE(
        AVERAGE('DataTable'[Revenue]),
        DATESINPERIOD('DataTable'[Date], CurrentDate, -12, MONTH)
    )
VAR ReductionFactor = SELECTEDVALUE('Factor Table'[Factor], 1)
VAR MonthDiff = DATEDIFF(CurrentDate, TODAY(), MONTH)
VAR BaseForecastRevenue = IF(
    ISBLANK('DataTable'[Revenue]),
    Last12MonthsAvgRevenue,
    'DataTable'[Revenue]
)
RETURN
    IF(
        ISBLANK('DataTable'[Revenue]),
        IF(
            MonthDiff > 12,
            BaseForecastRevenue * ReductionFactor,
            BaseForecastRevenue
        ),
        'DataTable'[Revenue]
    )

     

Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
1 year ago

Hello Tobz007,

 

Can you please try the following:

Dynamic 15Month Forecast Revenue = 
VAR CurrentDate = MAX('DataTable'[Date])
VAR Last12MonthsAvgRevenue = 
    CALCULATE(
        AVERAGE('DataTable'[Revenue]),
        DATESINPERIOD('DataTable'[Date], CurrentDate, -12, MONTH)
    )
VAR ReductionFactor = SELECTEDVALUE('Factor Table'[Factor], 1)
VAR MonthDiff = DATEDIFF(CurrentDate, TODAY(), MONTH)
VAR BaseForecastRevenue = IF(
    ISBLANK('DataTable'[Revenue]),
    Last12MonthsAvgRevenue,
    'DataTable'[Revenue]
)
RETURN
    IF(
        ISBLANK('DataTable'[Revenue]),
        IF(
            MonthDiff > 12,
            BaseForecastRevenue * ReductionFactor,
            BaseForecastRevenue
        ),
        'DataTable'[Revenue]
    )

 

Tobz007's avatar
Tobz007
Frequent Visitor
1 year ago

Thanks for this Sahir_Maharaj I made some modifications but your response was a great pointer.