Running Forecast based on Actual data

[KristyP]

Hi,

Been working on this one for a while. I will greaty appreciate some assistance. The data I have is:

• Two data points - with average monthly usage calculated
• Usage calculated to 31/12/2023

What I require is:

• Running forecast based on actual usage month to month. Bascially usage to 31/12/2023 plus average monthly usage for every month on an ongoing basis
• How the data is to be presented is below  - each row is a different serial number

Please see link for some mock data 

https://docs.google.com/spreadsheets/d/1xvNJTyI9NCGBF1A8as9FgcuwBezPH8LI/edit?usp=sharing&ouid=10732...

Any assistance is greatly appreciated.

[Ashish_Mathur]

Hi,

In another tab of that file, show the expected result very clearly.

[KristyP]

Hi,

I have updated the file with a second tab. The output needs to include the following columns A, C and N onwards.

[Ashish_Mathur]

Someone else will help you with this.

[v-kongfanf-msft]

Hi @KristyP ,

You can try below formula:

avg_ =
VAR monthdifference =
    DATEDIFF ( UsageData[2022 Read date], UsageData[2023 Read Date], MONTH )
RETURN
    DIVIDE (
        UsageData[2023 Read Date] - UsageData[2022 Read date],
        monthdifference
    )

ForecastedUsage =
VAR MonthsAhead =
    DATEDIFF ( TODAY (), DATE ( 2023, 12, 31 ), MONTH )
RETURN
    'UsageData'[2023 Usage] + ( 'UsageData'[average_] * MonthsAhead )

Best Regards,
Adamk Kong

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