Forecast by month aggregate to year problem

[Anonymous]

In a Power BI forecast report I would like to make a forecast based on data of previous years

These are my steps so far:

• In 3 base measures I calculate a percentage by period: 

% Actual Same Period Year -1 =
CALCULATE (
    Transactions[Actual],
    DATEADD ( 'Calendar - Transactiondate'[Date], -1, YEAR )
)
    / CALCULATE (
        Transactions[Actual],
        PARALLELPERIOD ( 'Calendar - Transactiondate'[Date], -1, YEAR )
    )

 (for "% Actual Same Period Year -2" and "% Actual Same Period Year -3" I have similar calcualtions)

• I create an average percentage on the previous percentages:

% AVG Actual Previous 3 Years =
 ( 'Transactions'[% Actual Same Period Year -1]
 + 'Transactions'[% Actual Same Period Year -2]
 + 'Transactions'[% Actual Same Period Year -3] )
 / 3 

• This average percentage is multiplied by the total year budget of the current year, this way I have a forecast amount of every month in the current year:

Forecast € All Year =
CALCULATE (
 Transactions[Budget Amount],
 ALL ( Transactions[FutureBookingIndicator] ),
 ALL ( 'Calendar - Transactiondate'[MonthNr] )
)
 * Transactions[% AVG Actual Previous 3 Years]

• Next I want for the months where actuals are available to see  the actual amount in the forescast measure and for the future months the calcalated forecast amounts. So I will have a mix of actual and forecast:

Forecast =
IF ( [Actual €] = 0, [Forecast € All Year], [Actual €] )

 So far everything works as expected, but…

• Next I want to be able to aggregate the forecast to year level.

The problem is that on total year (or on a visual where months are not displayed) I only see the actual amount, which is logical because Actual is not 0 on this level.

I tried to use the HASONEFILTER option to bypass this problem but I cannot figure out what the formule on the TRUE side must be:

Forecast =
IF (
    HASONEFILTER ( 'Calendar - Transactiondate'[MonthNr] ),
    IF ( [Actual €] = 0, [Forecast € All Year], [Actual €] ),
    <TRUE>
)

 Any suggestions? Or other possible solutions?

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may use a similar way as shown here.

[Anonymous]

@v-chuncz-msft: Figured it out!  Thank you.

It works with an extra measure on top of the Forecast measure

Aggregatable Forecast=
SUMX (
    SUMMARIZE ( Transactions, 'Calendar - Transactiondate'[MonthNr] ),
    [Forecast]
)

[Anonymous]

@v-chuncz-msft, Thank you for your reply, I am having trouble to translate the suggested solution to my situation

Could you please give me another suggestion?