Forum Discussion

Foxxon28's avatar
Foxxon28
Helper 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 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

 

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.

2 Replies

  • Alican_C's avatar
    Alican_C
    Resolver 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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.