Forum Discussion
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...
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
Super User
Super UserAnonymous,
See slicer below:
Ok, that is what I thought you meant. It didn't work, but what did work (and it somewhat of a pain - but it works), is I created a Filter table that I will have to recreate for each product in the slicer. Then, I copied the measures, and updated your Project Growth measure to reflect the correct data. Here is what I used to create the filter table in case you are curious:
Product Filter Table = FILTER('PBI Sample Data2', 'PBI Sample Data2'[Product] = "Product Name")
In addition, I am attempting to use your provided calculation to calculate the future with the average growth rate (which I have already calculated) over time. Here is what I plugged in to your formula for the growth rate but it is not working:
VAR vGrowthRate =
AVERAGEX(
KEEPFILTERS(VALUES('Calendar Table'[Year])),
CALCULATE([AS YoY Change])
)
When I plug in this formula, the result forecast for each year $10,995,139 - so it is not compounding the growth rate for the future years. When I type the percentage growth in the DAX it calculates correctly, but of course I want this percentage to be dynamic.
Thank you again for your help.