cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AldoJavier26
Frequent Visitor

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 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])))

 

1 ACCEPTED 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]
    )
)

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@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. 






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:

result.JPG

I've attached the sample PBIX 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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]
    )
)

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Thank you so much !

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.