Forecasting with own Formula

[Anonymous]

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

[v-deddai1-msft]

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]

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]

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

[Anonymous]

the forecast and target are to hit the same number

so the forecast will predict the increase over the remaining months on what needs to happen to hit that number

[v-deddai1-msft · Accepted solution]

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

[Ashish_Mathur]

Hi,

Share your data in a format that can be pasted in an MS Excel workbook.