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
Super UserAnonymous,
Would you clarify the following?
1. 2020 YOY change: (10,794,544 - 10,069,233) / 10,069,233 = 7.2%; the example shows 8.6%
2. How is Projected Growth of 10,939,606 calculated? This should be on line 2021, right?
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
- DataInsights
Anonymous,
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 vResultCould you please share the PBIX file for this solution?
