Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 full year forecast. For example, YTD actuals would be Jan-Aug + Sep-Dec (Prior Year) = Current Year Forecast.
With this current formula, I am running into trouble when trying to graph multiple years because the Forecast piece for my current year forecast is showing up on top of the prior year actuals. So if last year the revenue was $120M (10m/month). My bar graph reflecting 2020 is showing $160M, which is $120M + $40M.
My revenue measure are:
Revenue = SUM('Table1'[Revenue])
YTD = Calculate([Revenue],Table1[Month]=Jan || … Table1[Month]=Aug ||)
Projection = Calculate([Revenue],Table1[Month]=Sep|| … Table1[Month]=Dec)
Forecast FY21 = YTD+Projection
Revenue Forecast =
CALCULATE([Forecast FY21],
FILTER(ALL( 'Date'[Dates]),
'Date'[Dates] <= MAX( 'Date'[Dates])))
Solved! Go to Solution.
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]
)
)
Proud to be a Super User!
Paul on Linkedin.
@AldoJavier26 it will be easier if you share a sample pbix file, (remove sensitive information before sharing), use one drive/google drive to share the file.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
Proud to be a Super User!
Paul on Linkedin.
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]
)
)
Proud to be a Super User!
Paul on Linkedin.
Thank you so much !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |