Forum Discussion

Foxxon28's avatar
Foxxon28
Icon for Helper I rankHelper I
2 years ago
Solved

Forecast measure with fixed % increase

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 foreca...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Alican_C
Icon for Resolver II rankResolver II
2 years ago

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.