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.
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 ) )
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 ) )
Thanks a lot @Zubair_Muhammad! This helps a ton!
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 @harishreddys
Thanks,
Aaron
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
We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.
Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!
User | Count |
---|---|
164 | |
82 | |
76 | |
41 | |
40 |