cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashleylarsen Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

@ashleylarsen ,

 

You may try using TOPN.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

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

@ashleylarsen ,

 

You may try using TOPN.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
ashleylarsen Frequent Visitor
Frequent Visitor

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

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!