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

General Comment
Need Help
Show and Tell

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

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