Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Forecast with DAX

I am exhausted from trying to get this solution. I have data that is a customized forecast. I have created a dummy below. I am trying to calculate the projected future growth based on the estimated g...
Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    Anonymous,

     

    In your PROJECTED GRWTH measure, the second and third variables compare a date to a year. Change it to the following and it should work:

     

    PROJECTED GRWTH = 
VAR vCurYear =
    CALCULATE ( YEAR ( MAX ( 'PBI Sample Data2'[Process Date] ) ), ALL ('PBI Sample Data2') )
VAR vCurYearSales =
    CALCULATE ( SUMX ( 'PBI Sample Data2', 'PBI Sample Data2'[ACV+YTD Est.] ),  'Calendar Table'[Year] = vCurYear )
VAR vLastYearSales =
    CALCULATE (SUMX (  'PBI Sample Data2', 'PBI Sample Data2'[ACV+YTD Est.] ),  'Calendar Table'[Year] = vCurYear - 1 )
VAR vGrowthRate =
    DIVIDE ( vCurYearSales - vLastYearSales, vLastYearSales )
VAR vYearIncrement =
    MAX ( 'Calendar Table'[Year] ) - vCurYear
VAR vProjGrowth =
    vCurYearSales
        * POWER ( 1 + vGrowthRate, vYearIncrement )
VAR vResult =
    IF ( MAX ( 'Calendar Table'[Year] ) <= vCurYear, BLANK (), ROUND ( vProjGrowth, 0 ) )
RETURN
    vResult

     

DataInsights's avatar
DataInsights
Super User
5 years ago

Anonymous,

 

In your PROJECTED GRWTH measure, the second and third variables compare a date to a year. Change it to the following and it should work:

 

PROJECTED GRWTH = 
VAR vCurYear =
    CALCULATE ( YEAR ( MAX ( 'PBI Sample Data2'[Process Date] ) ), ALL ('PBI Sample Data2') )
VAR vCurYearSales =
    CALCULATE ( SUMX ( 'PBI Sample Data2', 'PBI Sample Data2'[ACV+YTD Est.] ),  'Calendar Table'[Year] = vCurYear )
VAR vLastYearSales =
    CALCULATE (SUMX (  'PBI Sample Data2', 'PBI Sample Data2'[ACV+YTD Est.] ),  'Calendar Table'[Year] = vCurYear - 1 )
VAR vGrowthRate =
    DIVIDE ( vCurYearSales - vLastYearSales, vLastYearSales )
VAR vYearIncrement =
    MAX ( 'Calendar Table'[Year] ) - vCurYear
VAR vProjGrowth =
    vCurYearSales
        * POWER ( 1 + vGrowthRate, vYearIncrement )
VAR vResult =
    IF ( MAX ( 'Calendar Table'[Year] ) <= vCurYear, BLANK (), ROUND ( vProjGrowth, 0 ) )
RETURN
    vResult

 

ebhomium's avatar
ebhomium
Regular Visitor
2 years ago

CAN ANYONE HELP WITH STEPS ON HOW TO RUN A FORECAST FOR YEAR 2023, 2024,2025, 2026 ON POWER BI.CHECK MY SCREENSHOT BELOW.