Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
You may try using TOPN.
https://community.powerbi.com/t5/Desktop/Dax-Measure-Fill-Down-Value/m-p/594415#M282232
You may try using TOPN.
https://community.powerbi.com/t5/Desktop/Dax-Measure-Fill-Down-Value/m-p/594415#M282232
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |