Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Forecast by month aggregate to year problem

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?

 

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      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?

    • Anonymous's avatar
      Anonymous
      Not applicable

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