Forum Discussion
Solved
Forecast help. Using remaining months from prior year for current full year forecast.
Forecast help. Using remaining months from prior year for current full year forecast. I am using prior year actuals as the forecasting portion for the remaining months to complete my current year ...
Sorry, I misunderstood the task. Try:
Forecast = IF( MAX ( 'Date'[Fiscal Year] ) <= YEAR ( TODAY() ), [.Sum Revenue], SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Date', 'Date'[Fiscal Year], 'Date'[Month Name] ), "_forecast", [Revenue FC based on Month Today] ), [_forecast] ) )
See if this works:
Create these measures.
Revenue FC based on Month Today =
VAR PY =
CALCULATE (
[.Sum Revenue],
FILTER (
ALL ( 'Date'[Fiscal Year] ),
'Date'[Fiscal Year]
= MAX ( 'Date'[Fiscal Year] ) - 1
)
)
VAR TDY =
IF ( MONTH ( TODAY () ) < 5, MONTH ( TODAY () ) + 8, MONTH ( TODAY () ) - 4 )
VAR Res =
IF ( MAX ( 'Date'[Fiscal Period] ) >= TDY, PY, [.Sum Revenue] )
RETURN
Res
And
Forecast =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( 'Date', 'Date'[Fiscal Year], 'Date'[Month Name] ),
"_forecast", [Revenue FC based on Month Today]
),
[_forecast]
)
To get:
I've attached the sample PBIX
Paul - Fiscal year 2021 & 2020, dont seem to show the full year actuals with this measure. Wanting the measure to give me these results:
2020 = $60,000
2021 = $120,000
2022 = $150,000
Sorry, I misunderstood the task. Try:
Forecast = IF( MAX ( 'Date'[Fiscal Year] ) <= YEAR ( TODAY() ), [.Sum Revenue], SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Date', 'Date'[Fiscal Year], 'Date'[Month Name] ), "_forecast", [Revenue FC based on Month Today] ), [_forecast] ) )Thank you so much !