Forum Discussion
Forecast From Selected Year
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.
Super UserAnonymous , 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.
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.