Use the most recent available value in forecasting formula with future dates

[ashleylarsen]

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

[v-chuncz-msft · Accepted solution]

@ashleylarsen ,

You may try using TOPN.

https://community.powerbi.com/t5/Desktop/Dax-Measure-Fill-Down-Value/m-p/594415#M282232

[ashleylarsen]

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!