Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Forecasting with own Formula

Hi, i have been asked to forecast the future usage of a service using formula created in excel but do this via powerbi
ive been asked to use this formula derived by a manager

Each month the usage of the service is entered, but then the remaining months in forecast column are calculated using a target and "Continous Value" value 

 

June for the purpose of this sample is last known month where data was entered.

 

to calculate the "Continuous Value", its taking the "Target" divide last months value ^ (1/remaining months in calendar year). 
to work out July forecast value is 

= (Target Value/June-21)^(1/Remaining Months in Calendar Year)

or
=(F2/B11)^(1/6)

 

for the forecast for each remaining month is the Month prior X the "Continuous Value" 
for July its

= B11*$F$3

for August its
= C12*$F$3

 

etc etc


so what i have failed to achieve is 

A) the measure for the "Continuous Value"

B) the Column formula for the "Forecast" 

 

any guidance would be appreciated

 

 

 

 

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ，

     

    Sorry I have misunderstood your requirement.Please use the following measure:

     

    Continuous Value =
VAR a =
    CALCULATE (
        LASTNONBLANKVALUE (
            'Table'[Merged],
            CALCULATE ( SUM ( 'Table'[Usage/Forecast] ) )
        ),
        ALL ( 'Table' )
    )
RETURN
    ( MAX ( 'Table 2'[Target] ) / a )
        ^ (
            1
                / (
                    12
                        - MONTH (
                            CALCULATE (
                                LASTNONBLANK ( 'Table'[Merged], CALCULATE ( SUM ( 'Table'[Usage/Forecast] ) ) ),
                                ALL ( 'Table' )
                            )
                        )
                )
        )


Forecast =
VAR a =
    CALCULATE (
        LASTNONBLANKVALUE (
            'Table'[Merged],
            CALCULATE ( SUM ( 'Table'[Usage/Forecast] ) )
        ),
        ALL ( 'Table' )
    )
VAR b =
    CALCULATE (
        LASTNONBLANK ( 'Table'[Merged], CALCULATE ( SUM ( 'Table'[Usage/Forecast] ) ) ),
        ALL ( 'Table' )
    )
RETURN
    IF (
        ISBLANK ( MAX ( 'Table'[Usage/Forecast] ) ),
        a
            * [Continuous Value]
                ^ ( MONTH ( MAX ( 'Table'[Merged] ) ) - MONTH ( b ) ),
        BLANK ()
    )

     

     

    For more details, please refer to the pbix file.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

6 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    There is someting misunderstood in your Continuous Value. What does the  last months value mean? The days from start of year to June-21?

     

    Would you please explain more about it.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      hi v-deddai1-msft last month is the last entered data

      so in a spreadsheet the last month is the data entered into column B
      so for the purpose of this example, the last month was June 2021

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        5 years ago

        Hi Anonymous ,

         

        How did you calculate 14720000/June 2021, June 2021 is not a number value, even though i use value function it still don't get right value in your screenshot.

         

        Best Regards,

        Dedmon Dai