Forum Discussion

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

Forecasting Same Period 2024 - YTD

Hi everyone, If anyone can help me to understand if there is a formula to use in DAX, to build a forecast/prediction with the current data value from Jan - June 2024 in order to predict a value fo...
General Comment
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi CuriousGuy001 ,

 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

 

 

Best Regards,

Clara Gong

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

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

    I am trying to figure out with this,

    Whenever I use the Moving average, the value goes downward after August, is there a way to keep steady? I am currently working those value from Jan - June values, 

    I am using this formula,

     

    Forecast Sales 3MA =
    VAR Period =
        DATESINPERIOD(
            'Date'[Date],
            MAX('Date'[Date]),
            -3,
            MONTH
        )
    VAR TotalSales =
        CALCULATE(
            [Actual Sales],
            Period
        )
    VAR Mon =
        CALCULATE(
            DISTINCTCOUNT('Date'[Month]),
            Period
        )
    RETURN
        DIVIDE(TotalSales, Mon)
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi CuriousGuy001 ,

       

      1. you could apply a correction factor, as mentioned in the link in the previous reply, by calculating the percentage difference between budget and sales, and then using this percentage difference as a correction factor for the future.

       

      2. you could try weighted moving averages: applying more weights to the most recent months so that they have a greater impact on the forecast. This helps to balance out the impact of declining sales in recent months. Below is a simple example that I hope will inspire you.

      Forecast Sales WeightedMA =
VAR Period1 = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -1, MONTH)
VAR Period2 = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -2, MONTH)
VAR Period3 = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH)
VAR Sales1 = CALCULATE([Actual Sales], Period1)
VAR Sales2 = CALCULATE([Actual Sales], Period2)
VAR Sales3 = CALCULATE([Actual Sales], Period3)
RETURN
    (Sales1 * 0.5 + Sales2 * 0.3 + Sales3 * 0.2) / (0.5 + 0.3 + 0.2)

       

       

      Best Regards,

      Clara Gong

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

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

        Hi Clara,

        Thank you for the help, but I tried the Forecast Sales WeightedMA that you provided and I think it is not going to work cause it is  recursive calculation. The best way is to use Fibonacci, but that is advanced to apply.