cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harishreddys Frequent Visitor
Frequent Visitor

Finance Forecast using DAX

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:

 

 

Sample.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Finance Forecast using DAX

Hi @harishreddys

 

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

 

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Finance Forecast using DAX

Hi @harishreddys

 

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

 

View solution in original post

Super User
Super User

Re: Finance Forecast using DAX

Highlighted
harishreddys Frequent Visitor
Frequent Visitor

Re: Finance Forecast using DAX

Thanks a lot @Zubair_Muhammad! This helps a ton! 

aaronnayan Frequent Visitor
Frequent Visitor

Re: Finance Forecast using DAX

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 832 guests
Please welcome our newest community members: