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

 

Try my new Power BI game Cross the River

View solution in original post

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

 

Try my new Power BI game Cross the River

View solution in original post

Super User
Super User

Re: Finance Forecast using DAX

@harishreddys

 

 

1009.png

Try my new Power BI game Cross the River
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

jeremystern Frequent Visitor
Frequent Visitor

Re: Finance Forecast using DAX

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:

 

  1. The first two measures in your Step 1 make sense to me as you are using a total revenues result to establish a CAGR (compound annual growth rate). However, wouldn't ALLSELECTED be a better function rather than ALL as we would only want results from the selected dates?
  2. If I want the historical total revenues calculated during the period I will use for the forecast, is FIRSTNONBLANK and LASTNONBLANK the best way to do this as then any null value in the historical data will then have a forecasted value created even though it is already in the past. For example, assuming today's date is Oct 2019:
MonthYearTotal RevenueForecast
Jan20192 
Feb20196 
Mar2019 11
Apr20193 


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?

 

  1. My understanding of the the result from the two Step 1 measures, FirstYear and LastYear, are the total revenues for that given time period, correct, or in the context of the conventional CAGR calculation, the (End Value/Start Value)^(1/Periods)-1. But in Step 2, those measures are used to compute CAGR using the End Value (LastYear) - Start Value (FirstYear) to get the number of years (No_of_Years) variable. In this case, the No_of_Years variable (Period) will give the result of the total revenue of the End Value (LastYear) minus the Start Value (FirstYear) which won't be a period rather it will be the revenue amount. Is this correct? Shouldn't the Period be the first year minus the current year to get the number of years (the Period)?
  2. In Step 4, it seems to me that once again we are mixing the year number (2019) for the sum of the revenues for that year again and the same for the first year. Am I missing something or are we changing the meaning of the measures from the total revenues instead of the year number?
  3. I want to forecast my model beyond the end of the current year. What would it take to modify how far out this forecast will go?

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)