Forum Discussion
Use the most recent available value in forecasting formula with future dates
Hello,
I am trying to use the most recent value (date is 2017) in a formula to obtain a forecast for 2018, 2019 and 2020.
Right now, the only way I am able to get the forecast to work is to create a variable for each year of my forecast. That is to say that, in order to get the most recent value to push on into dates in the future I use:
2018 N R Forecast =
IF( ISBLANK( [Total New R]),
CALCULATE( [Total New R], SAMEPERIODLASTYEAR('Fiscal Year'[Dates])),
BLANK())
Then, I end up with multiple variables: 2018 N R Forecast, 2019 N R Forecast, and 2020 N R Forecast.
Since I want to use the same value repeatedly for future dates, isn't there another way other than the SAMEPERIODLASTYEAR? I have tried creating a calculated column using MAX, but that did not work in the forumla.
Thanks,
Ashley
2 Replies
- v-chuncz-msft
Community Support
- ashleylarsenFrequent Visitor
Thanks v-chuncz-msft ,
This worked well with my measures. It took me a while to understand what's being done. It's that first line,
VAR y = SelectedValue(DATE) combined into the Filter, FILTER(ALLSELECTED(DATES), DATES <= y), which means that the date changes dynamically.Great help!