Forum Discussion

KristyP's avatar
KristyP
Icon for Helper I rankHelper I
2 years ago

Running Forecast based on Actual data

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=107327280152538851278&rtpof=true&sd=true

 

Any assistance is greatly appreciated.

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • KristyP's avatar
      KristyP
      Icon for Helper I rankHelper I
      2 years ago

      Hi,

       

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