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
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 vResultYes! It works!!! Thank you Thank you Thank you!!!! 😀
CAN ANYONE HELP WITH STEPS ON HOW TO RUN A FORECAST FOR YEAR 2023, 2024,2025, 2026 ON POWER BI.CHECK MY SCREENSHOT BELOW.
