Finance Forecast using DAX

Hi Anonymous

See the attached file.

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 )
    )