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
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.