Forecast measure with fixed % increase

[Foxxon28]

Dear reader,

I have a line chart that has data up untill the current month.

Using a YTD / YOY calculation I have a fixed % Change measure.

I want to use that [% Change YOY] measure to forecast the rest of the year, starting from my max [Month].

My line chart has a cumulative [Sales] total of:

$ 100 in May.. What I expect:

June = $100 * [% Change YOY]

Juli = June *  [% Change YOY] 

Aug = Juli *  [% Change YOY] 

etc.

Could you help me write this measure? 🙂

Kind regards,

Daniël

[Anonymous · Accepted solution]

Hi @Foxxon28 ,

I create a table and a measure as you mentioned.

% Change YOY = AVERAGE('Table'[Change YOY])

Then I create a measure and here is the DAX code.

Measure = 

VAR MaxMonth = MAX('Table'[Month])
VAR ChangeYOY = [% Change YOY]
VAR BaseSales = CALCULATE(SUM('Table'[Change YOY]), 'Table'[Month] = MaxMonth)
RETURN
    SUMX(
        ADDCOLUMNS(
            CALENDAR(MaxMonth, DATE(YEAR(MaxMonth), 12, 31)),
            "ForecastedSales", 
            BaseSales * POWER(1 + ChangeYOY, MONTH([Date]) - MONTH(MaxMonth))
        ),
        [ForecastedSales]
    )

Best Regards

Yilong Zhou

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

[Alican_C]

Hi, 

Assuming you already have the [% Change YOY] measure, if not, you can create it with something like this:

% Change YOY =
DIVIDE(

SUM('Sales'[Sales]) - CALCULATE(SUM('Sales'[Sales]), SAMEPERIODLASTYEAR('Date'[Date])),
CALCULATE(SUM('Sales'[Sales]), SAMEPERIODLASTYEAR('Date'[Date]))
)

Step 2: Create the Forecast Measure:

Forecasted Sales =
VAR MaxMonth = MAX('Date'[Month])
VAR YoYChange = [% Change YOY]
VAR CumulativeSales = CALCULATE(SUM('Sales'[Sales]), 'Date'[Month] <= MaxMonth)
VAR ForecastSales =
GENERATE(
FILTER(
ALL('Date'),
'Date'[Month] > MaxMonth && 'Date'[Year] = YEAR(TODAY())
),
VAR PreviousMonthSales =
IF(
'Date'[Month] = MaxMonth + 1,
CumulativeSales,
CALCULATE(SUM('Sales'[Sales]), 'Date'[Month] = EARLIER('Date'[Month]) - 1) * (1 + YoYChange)
)
RETURN
SELECTCOLUMNS(
'Date',
"Month", 'Date'[Month],
"Forecasted Sales", PreviousMonthSales * (1 + YoYChange)
)
)
RETURN
IF(
'Date'[Month] <= MaxMonth,
CumulativeSales,
SUMX(
FILTER(ForecastSales, [Month] <= 'Date'[Month]),
[Forecasted Sales]
)
)

Finally: Add the Forecasted Sales measure to your line chart & make sure your date table has a continuous range covering the entire year.