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

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Still not working. Here is my date table DAX. I have it going all the way to 2025, and it is connected to the sales date and dollars in the other table. I even tried to add "dummy dates" to my other table and it still would not pull in the future years in my visualization. 

Calendar Table = VAR BaseCalendar = CALENDAR (DATE(2012, 1, 1), DATE(2025, 12, 31)) RETURN GENERATE ( BaseCalendar, VAR BaseDate = [Date] VAR YearDate = YEAR (BaseDate) VAR MonthNumber = MONTH (BaseDate) VAR YearMonthNumber = YearDate * 12 + MonthNumber -1 VAR QTR = CONCATENATE("Q",ROUNDUP(MONTH([Date])/3,0)) RETURN ROW ( "Year", YearDate, "Month Number", MonthNumber, "Month", FORMAT (BaseDate, "mmmm"), "Year Month Number", YearMonthNumber, "Year Month", FORMAT (BaseDate, "mmm yy"), "QTR", QTR))
 
I feel like I am missing an "IFISBLANK" operator somewhere for the current year sales...
DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
5 years ago

Anonymous,

 

Would you be able to share your pbix? You can replace sensitive data with sample data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Yes let me work on this and I will lob it your way in a few days!