Forum Discussion
Solved
Calculating future months using latest actuals based on expected growth
Hi I am stuck with this and would need help on the below. Month YTD Actuals Expected growth for next mont, based on actuals from Current month Expected actuals (forecasted actuals) A...
Hi mixue100 ,
this is the measure for the forecasts. Please hit the thumbs up & mark it as a solution if it helps you. Thanks.
Forecasts =VAR LastActualMonth =CALCULATE (MAX ( 'Table'[Month] ),ALL ( 'Table' ),'Table'[YTD Actuals] <> BLANK ())VAR LastActual =CALCULATE (SUM ( 'Table'[YTD Actuals] ),ALL ( 'Table' ),'Table'[Month] = LastActualMonth)VAR CurrentMonth = MAX('Table'[Month])VAR Product_Growth =CALCULATE(PRODUCTX(FILTER('Table','Table'[YTD Actuals]=BLANK() && 'Table'[Month] <= CurrentMonth),'Table'[Expected growth] + 1),ALL())RETURNProduct_Growth * LastActualAnd here there is the link to the pbi file:
mangaus1111
Solution Sage
Solution SageHi mixue100 ,
this is the measure for the forecasts. Please hit the thumbs up & mark it as a solution if it helps you. Thanks.
Forecasts =
VAR LastActualMonth =
CALCULATE (
MAX ( 'Table'[Month] ),
ALL ( 'Table' ),
'Table'[YTD Actuals] <> BLANK ()
)
VAR LastActual =
CALCULATE (
SUM ( 'Table'[YTD Actuals] ),
ALL ( 'Table' ),
'Table'[Month] = LastActualMonth
)
VAR CurrentMonth = MAX('Table'[Month])
VAR Product_Growth =
CALCULATE(
PRODUCTX(
FILTER('Table',
'Table'[YTD Actuals]=BLANK() && 'Table'[Month] <= CurrentMonth
),
'Table'[Expected growth] + 1
),
ALL()
)
RETURN
Product_Growth * LastActual
And here there is the link to the pbi file: