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.

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.