Forum Discussion
Forecast with DAX
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
10,939,606 is a custom calculation based on actual ytd sales + expected sales through the end of the year. It should really be in the YTD column. Sorry I had to recreate this in Excel. It should read:
Year YTD Sales YOY change Projected Growth based on 2020 grwth
2020 $10,939,606 8.6%
2021 $11,885,214 This is based on YOY Change * YTD Sales previous yr
2022 $12,912,559 Based on Cell Above Grwth * YOY Change
....and so on
Super UserAnonymous,
Try this measure:
Projected Growth =
VAR vCurYear =
CALCULATE ( YEAR ( MAX ( SalesProjection[Date] ) ), ALL ( SalesProjection ) )
VAR vCurYearSales =
CALCULATE ( SUM ( SalesProjection[YTD Sales] ), Dates[Year] = vCurYear )
VAR vLastYearSales =
CALCULATE ( SUM ( SalesProjection[YTD Sales] ), Dates[Year] = vCurYear - 1 )
VAR vGrowthRate =
DIVIDE ( vCurYearSales - vLastYearSales, vLastYearSales )
VAR vYearIncrement =
MAX ( Dates[Year] ) - vCurYear
VAR vProjGrowth =
vCurYearSales
* POWER ( 1 + vGrowthRate, vYearIncrement )
VAR vResult =
IF ( MAX ( Dates[Year] ) <= vCurYear, BLANK (), ROUND ( vProjGrowth, 0 ) )
RETURN
vResult
Could you please share the PBIX file for this solution?
