Forum Discussion

AldoJavier26's avatar
AldoJavier26
Frequent Visitor
4 years ago
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 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])))

 

General Comment
Need Help
Show and Tell
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

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

     

     

     

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    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. 

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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