Forecasting Same Period 2024 - YTD

[CuriousGuy001]

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 for the remaning of month of the year in the same period. I do understand is hard and most of the video and answer I have found is taking their forecast from previous year. There is visual analytics tool in the visualization pane, but my manager doesn't feel confident that is given a "true" result or what he is expecting from. Any possible solution that I can test out would be greatly appreciate it.

[Anonymous]

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]

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]

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]

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. 

[Anonymous]

Hi @CuriousGuy001 ,

The Forecast measure is a very advanced piece of DAX code that would require a full article in itself. You can find detailed information on how to reallocate budgets at different levels of granularity in the video https://www.sqlbi.com/tv/budgeting-with-power-bi-pass-austria/.

In this article, the forecast measure is used, explaining how to calculate the next measure: the remaining forecast. You can modify it to suit your own data.
Showing actuals and forecasts in the same chart with Power BI - SQLBI

Also, if you have your own rules to predict the values, you can give me some example data and tell me its rules to better help you solve the problem. Here are some similar examples that I hope will help you.

Solved: Running Increase of Forecast based on Actuals - Microsoft Fabric Community

excel formula - How to calculate Projected YTD in Power BI using DAX - Stack Overflow

If your Current Period does not refer to this, please clarify in a follow-up reply.

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.