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.
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:
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.
Solved! Go to Solution.
Hi @Anonymous
Hope it helps
Here are the steps
Step#1 Determine the First and Last Years with Actual data using these MEASURES
FirstYear = FIRSTNONBLANK ( ALL ( YearTable[Year] ), CALCULATE ( SUM ( MeasureTable[Revenue] ) ) )
LastYear = LASTNONBLANK ( ALL ( YearTable[Year] ), CALCULATE ( SUM ( MeasureTable[Revenue] ) ) )
Step#2 Determine the CAGR
CAGR = VAR Lastyear = [LastYear] VAR Firstyear = [FirstYear] VAR No_of_Years = [LastYear] - [FirstYear] RETURN POWER ( DIVIDE ( CALCULATE ( SUM ( MeasureTable[Revenue] ), YearTable[Year] = Lastyear ), CALCULATE ( SUM ( MeasureTable[Revenue] ), YearTable[Year] = Firstyear ) ), 1 / No_of_Years ) - 1
Step#3: Get the Forecast
Forecast = VAR Lastyear = [LastYear] VAR No_of_Years = SELECTEDVALUE ( YearTable[Year] ) - [LastYear] RETURN IF ( SELECTEDVALUE ( YearTable[Year] ) > Lastyear, CALCULATE ( SUM ( MeasureTable[Revenue] ), YearTable[Year] = Lastyear ) * POWER ( ( 1 + [CAGR] ), No_of_Years ) )
Hi @Anonymous
Hope it helps
Here are the steps
Step#1 Determine the First and Last Years with Actual data using these MEASURES
FirstYear = FIRSTNONBLANK ( ALL ( YearTable[Year] ), CALCULATE ( SUM ( MeasureTable[Revenue] ) ) )
LastYear = LASTNONBLANK ( ALL ( YearTable[Year] ), CALCULATE ( SUM ( MeasureTable[Revenue] ) ) )
Step#2 Determine the CAGR
CAGR = VAR Lastyear = [LastYear] VAR Firstyear = [FirstYear] VAR No_of_Years = [LastYear] - [FirstYear] RETURN POWER ( DIVIDE ( CALCULATE ( SUM ( MeasureTable[Revenue] ), YearTable[Year] = Lastyear ), CALCULATE ( SUM ( MeasureTable[Revenue] ), YearTable[Year] = Firstyear ) ), 1 / No_of_Years ) - 1
Step#3: Get the Forecast
Forecast = VAR Lastyear = [LastYear] VAR No_of_Years = SELECTEDVALUE ( YearTable[Year] ) - [LastYear] RETURN IF ( SELECTEDVALUE ( YearTable[Year] ) > Lastyear, CALCULATE ( SUM ( MeasureTable[Revenue] ), YearTable[Year] = Lastyear ) * POWER ( ( 1 + [CAGR] ), No_of_Years ) )
This is great, however when I tried to review data by product I received this error:
Couldn't load data for visual
MdxScript(Model) (155,5) Calculation error in measure 'Sales Forecast'[CAGR]: An argument of function 'POWER' has the wrong data type or the result is too large or too small.
If I use a slicer by product the forecast is blank.
Any ideas?
Hi,
This is a really interesting method and it sort of works for me but my results are a little off and I have some questions, if you don't mind:
Month | Year | Total Revenue | Forecast |
Jan | 2019 | 2 | |
Feb | 2019 | 6 | |
Mar | 2019 | 11 | |
Apr | 2019 | 3 |
etc.
Wouldn't it be better to use MIN and MAX to get the total revenues and how would I get it to not forecast the blank months that are before the end date?
I'm very sorry for the long winded question on an older post but I am trying to get it to work and it is giving me strange results (and errors in some instances).
Thanks so much in advance for chiming in!
Jeremy
This solution is brillant!
Is there a way, instead of having 2015-2017 like in the example, to have 2015 - 2016 data?
My Data set goes from 2016 - 2019 however 2019 is not finished so i would only want the last year to be 2018 as its finished..
"Might be to do with the Last year calc"
@Zubair_Muhammad @Anonymous
Thanks,
Aaron
@Anonymous
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |