Forum Discussion

7ballp25's avatar
7ballp25
Regular Visitor
4 years ago
Solved

CAGR HELP! Forecasting using Current data

I literally forgot how to do this, please help.    I have a table that has 2018-2022 Year sales. I created a whatifparameter from 0-100% to be the CAGR value. Focusing only on 2022 sales, I want t...
cagr
DAX
Forecasting
Help me please
Help Request
Urgent Help
what if parameters
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi 7ballp25 
    Here is a sample file with the solution https://we.tl/t-UvJsRpmiYd

    Forcasted Sales = 
VAR LastAvialableYear = 
    MAX ( Data[Year] ) 
VAR LastAvailableSales = 
    CALCULATE ( SUM ( Data[Sales] ),'Data'[Year] = LastAvialableYear )
VAR CurrentYear = 
    MAX ('New Data'[Year] )
VAR YearDifference =
    CurrentYear - LastAvialableYear
VAR SelectedPercent =
    SELECTEDVALUE ( Parameter[Selected %] )
RETURN
    LastAvailableSales * POWER ( 1 + SelectedPercent, YearDifference )

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi 7ballp25 
Here is a sample file with the solution https://we.tl/t-UvJsRpmiYd

Forcasted Sales = 
VAR LastAvialableYear = 
    MAX ( Data[Year] ) 
VAR LastAvailableSales = 
    CALCULATE ( SUM ( Data[Sales] ),'Data'[Year] = LastAvialableYear )
VAR CurrentYear = 
    MAX ('New Data'[Year] )
VAR YearDifference =
    CurrentYear - LastAvialableYear
VAR SelectedPercent =
    SELECTEDVALUE ( Parameter[Selected %] )
RETURN
    LastAvailableSales * POWER ( 1 + SelectedPercent, YearDifference )