Forum Discussion

edson_souza's avatar
edson_souza
Regular Visitor
9 years ago
Solved

Average and Forecasting

I have the table below: Month Budget Realized Oct $489943,13 $515707,20 Nov $883368,63 $643675,04 Dec $637668,28 $681661,99 Jan $1931667,93 $626560,46 Fev $1289529,29 $748939,90 Mar $2372920...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi edson_souza,

     

    Create the following measures:

    Forecast_Total =
IF (
    SUM ( Average_Forecast[Realized] ) = 0;
    CALCULATE ( [Average realized]; ALLSELECTED ( Average_Forecast ) );
    SUM ( Average_Forecast[Realized] )
)

     

    Forecast =
IF (
    SUM ( Average_Forecast[Realized] ) = 0;
    CALCULATE ( [Average realized]; ALLSELECTED ( Average_Forecast ) );
    SUMX (
        SUMMARIZE (
            Average_Forecast;
            Average_Forecast[Month];
            "Forecasted"; Average_Forecast[Forecast_Total]
        );
        [Forecasted]
    )
)

    The first one is just auxiliary to calculate the total value for the year, then just add the Forecast to your table should give expected result.

     

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
9 years ago

Hi edson_souza,

 

First of all I would make your month column into a date column with the last day of the month as a value this will allow you to use the Date features and the YTD QTD MTD calculations then add this to your model and you should get what you need:

 

Average realized = 
VAR Realized =
    TOTALYTD ( SUM ( Average_Forecast[Realized] ); Average_Forecast[Month] )
VAR Months_Count =
    CALCULATE (
        DISTINCTCOUNT ( Average_Forecast[Month] );
        FILTER (
            ALLSELECTED ( Average_Forecast );
            Average_Forecast[Month] <= MAX ( Average_Forecast[Month] )
        );
        FILTER ( ALLSELECTED ( Average_Forecast ); Average_Forecast[Realized] > 0 )
    )
RETURN
    DIVIDE ( Realized; Months_Count )

FORMULA ALSO WORKS FOR MORE THAN ONE ROW PER MONTH

As you can see below I have a column with the Average and also the count of months that is the second part of the divide so you can see that the months with 0 are not counted on the division. Used the Hierarchy to only show month in the calculations.

 

 

Regarding your second calculation not sure what you want and how you want to calculate. Where do you get the actuals and what value you want to assume for the month?

 

Regards,

MFelix

 

 

  • edson_souza's avatar
    edson_souza
    Regular Visitor
    9 years ago

    Thank you, MFelix.

    Item 1 solved with the formula that you sent, although the values were not equal monthly, the general average was correct (644.821,53).

     

    For item 2, the forecast would be: when realized, the same as realized. When there is no, equal to the monthly average already found. According to the table below:

    • MFelix's avatar
      MFelix
      Super User
      9 years ago
      Hi edson_souza,

      I didn't used the decimal places that's way the values are not exactly the same.

      To make this change you want just use this.formula

      FORECASTED = IF ( SUM(TABLE[REALIZED])= 0; [AVERAGE_REALIZED]; SUM( TABLE[REALIZED]))

      Use the previous measure in your 2nd argument in your formula.

      Regards
      MFelix
      • edson_souza's avatar
        edson_souza
        Regular Visitor
        9 years ago

        Hi MFelix

         

        I tested it this way, but the unrealized month continues with a value equal to 0.00.

        I noticed in your table "Average realized" has value in every month, mine only where realized.