Calculating future months using latest actuals based on expected growth

Question

Hi&nbsp;&nbsp;I am stuck with this and would need help on the below.&nbsp;&nbsp;MonthYTD ActualsExpected growth for next mont, based on actuals from Current monthExpected actuals (forecasted actuals)Actuals or ForecastSourceJan-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5005%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 525&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 500ActualsFeb-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,0002%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,000ActualsMar-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,2006%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,272&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,200ActualsApr-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 60010%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 660&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 600ActualsMay-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8000%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 800&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 800ActualsJun-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 900-5%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 855&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 900ActualsJul-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200-3%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 194&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200ActualsAug-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10010%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100ActualsSep-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3000%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300ActualsOct-22&nbsp;Not available yet&nbsp;-5%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 285&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 285ForecastNov-22&nbsp;Not available yet&nbsp;6%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 302&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 302forecastDec-22&nbsp;Not available yet&nbsp;10%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 332&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 332forecastJan-23&nbsp;Not available yet&nbsp;0%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 332&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 332Forecast&nbsp;&nbsp;I have actuals until September. I would to have a measure that helps me to derive at the column in blue font - that is having YTD actuals + forecasted.&nbsp;&nbsp;E.g. October's expected figure (blue) is based on September's actuals + growth of -5 %i.e. actuals of 300 from september minus 5 % = 285&nbsp;&nbsp;November's expected figures (blue) is based on october's expected figures + growth of +6%expected actuals of october (285) plus growth of 6% gives me expected november figures of 302.&nbsp;&nbsp;&nbsp;Appreciate your help!&nbsp;&nbsp;

mangaus1111 · Accepted Answer

Hi&nbsp;mixue100&nbsp;,&nbsp;this is the measure for the forecasts.&nbsp;Please hit the thumbs up &amp; mark it as a solution if it helps you. Thanks.&nbsp;Forecasts = VAR LastActualMonth = &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX ( 'Table'[Month] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL ( 'Table' ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Table'[YTD Actuals] &lt;&gt; BLANK ()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR LastActual = CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM ( 'Table'[YTD Actuals] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL ( 'Table' ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Table'[Month] = LastActualMonth&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR CurrentMonth = MAX('Table'[Month])VAR Product_Growth = CALCULATE(&nbsp;PRODUCTX(&nbsp; &nbsp; &nbsp; &nbsp; FILTER('Table',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Table'[YTD Actuals]=BLANK() &amp;&amp; 'Table'[Month] &lt;= CurrentMonth&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; 'Table'[Expected growth] + 1&nbsp; &nbsp; ),&nbsp; &nbsp; ALL()) RETURNProduct_Growth * LastActual&nbsp;&nbsp;And here there is the link to the pbi file:&nbsp;https://1drv.ms/u/s!Aj45jbu0mDVJiGdkR76ccaoHdFht?e=rAmtHb

Forum Discussion

Calculating future months using latest actuals based on expected growth

2 Replies

Related content

Calculate Actual booking

Expression.SyntaxError: Token ',' expected.

Dax Switch Calculation not working as expected

Actual quadruple how to fix?

Calculating YoY without having actual dates

Month	YTD Actuals	Expected growth for next mont, based on actuals from Current month	Expected actuals (forecasted actuals)	Actuals or Forecast	Source
Jan-22	500	5%	525	500	Actuals
Feb-22	1,000	2%	1,020	1,000	Actuals
Mar-22	1,200	6%	1,272	1,200	Actuals
Apr-22	600	10%	660	600	Actuals
May-22	800	0%	800	800	Actuals
Jun-22	900	-5%	855	900	Actuals
Jul-22	200	-3%	194	200	Actuals
Aug-22	100	10%	110	100	Actuals
Sep-22	300	0%	300	300	Actuals
Oct-22	Not available yet	-5%	285	285	Forecast
Nov-22	Not available yet	6%	302	302	forecast
Dec-22	Not available yet	10%	332	332	forecast
Jan-23	Not available yet	0%	332	332	Forecast