Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Forecast From Selected Year

Hi Experts,

I have a revenue data from 2017 to 2030. However I want to select a specific year and calculate revenue increased per year.
Example if I take 2021 as a base year with sales and want to calculate yearly increase of 7% each year, that is in 2022 7% increase of 2021 and in 2023 7 % increase of 2022. This calculation of increase each year should continue untill 2030 as a forecast. 
How can I achieve this?

 

Year Revenue
20171000
20181500
20191750
20201100
20211850
20222050
20232025
20241075
20252000
20262050
20272100
20282150
20292200
20302250
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous , bhanu_gautam, thank you for your prompt reply!

     

    For your requirements, please try as follows:

    • Create a separate calculated table containing all year values:

    YearsTable = DISTINCT('Table'[Year])

     

    • Then create a slicer and drag it to the slicer field:

              

    • Next, create a measure to calculate yearly increase of 7% each year from selected year: 

    Sales Forecast = VAR SelectedYear = SELECTEDVALUE(YearsTable[Year])
    VAR StartRevenue =CALCULATE(
    MAX('Table'[Revenue]),
    FILTER('Table', 'Table'[Year] = SelectedYear)
    )
    VAR CurrentYear = MAX('Table'[Year])
    VAR PreviousForecast = CALCULATE(
    MAX('Table'[Revenue]),
    FILTER(ALL('Table'), 'Table'[Year] = CurrentYear - 1)
    )
    RETURN
    IF(
    ISBLANK(SelectedYear),
    MAX('Table'[Revenue]),
    IF(
    CurrentYear = SelectedYear,
    StartRevenue,
    IF(
    CurrentYear > SelectedYear,
    PreviousForecast * 1.07,
    MAX('Table'[Revenue])
    )))

    Test result for your reference:

    Best regards,

    Joyce

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

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    Anonymous , You can use paramteres for this

    Click on "New Parameter" and create a parameter for the base year. Name it something like "BaseYearParameter".
    Set the data type to "Whole Number" and specify the range of years (e.g., from 2017 to 2030).

     

    Create a new table for forecasted value

    ForecastedRevenue =
    VAR BaseYear = SELECTEDVALUE(BaseYearParameter[BaseYearParameter])
    VAR BaseRevenue = CALCULATE(SUM('Revenue'[Revenue]), 'Revenue'[Year] = BaseYear)
    VAR GrowthRate = 0.07
    RETURN
    ADDCOLUMNS(
    FILTER(
    GENERATESERIES(BaseYear, 2030, 1),
    [Value] > BaseYear
    ),
    "Forecasted Revenue",
    BaseRevenue * POWER(1 + GrowthRate, [Value] - BaseYear)
    )

     

    Add a slicer visual to your report.
    Add the "BaseYearParameter" to the slicer

     

    Create a new line chart or any other visual to display the forecasted revenue.
    Add the "Year" column from the "ForecastedRevenue" table to the X-axis.
    Add the "Forecasted Revenue" column to the Y-axis.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello bhanu_gautam 

      I am receiving error message while creating new table in DAX 

      The arguments in the GenerateSeries function cannot be empty.

      I think it is because of selectedvalue function. Is there any other way to solve it.

      Thank you for help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous , bhanu_gautam, thank you for your prompt reply!

     

    For your requirements, please try as follows:

    • Create a separate calculated table containing all year values:

    YearsTable = DISTINCT('Table'[Year])

     

    • Then create a slicer and drag it to the slicer field:

              

    • Next, create a measure to calculate yearly increase of 7% each year from selected year: 

    Sales Forecast = VAR SelectedYear = SELECTEDVALUE(YearsTable[Year])
    VAR StartRevenue =CALCULATE(
    MAX('Table'[Revenue]),
    FILTER('Table', 'Table'[Year] = SelectedYear)
    )
    VAR CurrentYear = MAX('Table'[Year])
    VAR PreviousForecast = CALCULATE(
    MAX('Table'[Revenue]),
    FILTER(ALL('Table'), 'Table'[Year] = CurrentYear - 1)
    )
    RETURN
    IF(
    ISBLANK(SelectedYear),
    MAX('Table'[Revenue]),
    IF(
    CurrentYear = SelectedYear,
    StartRevenue,
    IF(
    CurrentYear > SelectedYear,
    PreviousForecast * 1.07,
    MAX('Table'[Revenue])
    )))

    Test result for your reference:

    Best regards,

    Joyce

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