What is the optimal way to calculate 5 year forecast using DAX in Power BI.
Consider the following example:
Company X has two tables, namely Year table (has Year data from 2015 to 2022) and Measure table (has year and revenue data form 2015 to 2017). Year table is linked to Measure table using tabular relationships. Check the picture below for more details:
Convectional way of calculating revenue forecast:
Step 1: Calculate CAGR (compound annual growth rate) using formula (End Value/Start Value)^(1/Periods) -1. In the example above it comes up to 9.5%
Step 2: Calculate Forecast.
Revenue_2018 = Revenue_2017 *(1+CAGR)
Revenue_2019 = Revenue_2017 *(1+CAGR)^2
Revenue_2020 = Revenue_2017 *(1+CAGR)^3
Revenue_2021 = Revenue_2017 *(1+CAGR)^4
Revenue_2022 = Revenue_2017 *(1+CAGR)^5
Question: What is the best/optimal way to accomplish this calculation in Power BI using DAX.