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 t...
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.

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.