Forum Discussion

mehlenbae2's avatar
mehlenbae2
Helper II
2 years ago
Solved

Filling Missing Forecast Numbers in Matrix Analysis

My boss has tasked me with filling in missing forecast numbers in my matrix. Currently, we only have "Actuals" data from January 2024 to July 2024, leaving gaps that need to be filled with forecasted numbers based on this year's historical data. Typically, we have future budget data for the year (found under the "Type" column), which is filled in accordingly. However, the "Actuals" values for August 2024 to December have not been recorded yet.

 

To address this, I need to find a way that can forecast data for the remainder of the year when there is no "Actuals" or "Budget" data available. This forecast should be based on trends observed in previous data from the same year. It is worth noting that there is a Year and Quarter slicer on the right of my page, and my matrix utilizes the sum of the "SignedValue" column in my "Raw Data" table.

 

In addition, I would like all forecasted data to be displayed in a different color than red or green, to clearly indicate that it is only forecast data.

 

 

Here is a preview of the "Raw Data" table:

 

Matrix: 

 

 

Pivot Table in Excel:

 

 

How can I do this?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi mehlenbae2 ,
    As you can see blank value is a calculated column and not a metric, we want a specific value when populating the value and not a value that filters with the context, sorry for the confusion as I didn't make it clear that blank value is a calculated column.

    blank value = AVERAGEX(FILTER('sample_data_for_forecast','sample_data_for_forecast'[Month]<8),'sample_data_for_forecast'[SignedValue])

    Additionally you can use Signed value to make judgements, giving different colours to indicate that this is the predicted value.

     

     

     

    If you have any other queries you can look at my uploaded pbix file, I hope it helps you with your queries!

     

     

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

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

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi mehlenbae2 ,

    We can try to do this using a calculated column to first get the month or extreme for which we don't have a value.

     

    Month = MONTH('sample_data_for_forecast'[Date])

     

    Use the average instead of our blank values to populate the values.

     

    blnak value = AVERAGEX(
     FILTER('sample_data_for_forecast',
     'sample_data_for_forecast'[Month]<8),'sample_data_for_forecast'[SignedValue])

     

    We make a judgement call and fill in the value we created if it's blank, or return to the original value if it's not.

     

    total value = 
 IF(
    ISBLANK('sample_data_for_forecast'[SignedValue])&&'sample_data_for_forecast'[Month]>=8,
    [blnak value],
    [SignedValue])

     

    Finally, use the if statement to determine in which column the null value exists, and then enter the conditional format to modify the colour to complete your needs.

     

    Column = IF(
    ISBLANK('sample_data_for_forecast'[SignedValue]),
    1,
    0
)

     

     

     

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

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

    • mehlenbae2's avatar
      mehlenbae2
      Helper II
      2 years ago

      Hi there,

       

      Thank you for responding. I tried your method and it doesn't seem to work. Here is what I did:

       

      Month = MONTH('Raw Data'[Date])
       
      blnak value = AVERAGEX(
           FILTER('Raw Data',
           'Raw Data'[Month]<8),'Raw Data'[SignedValue])
       
      total value =
       IF(
          ISBLANK('Raw Data'[SignedValue])&&'Raw Data'[Month]>=8,
          [blnak value],
          [SignedValue])
       

       

      Please let me know if I am doing anything wrong.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi mehlenbae2 ,
        As you can see blank value is a calculated column and not a metric, we want a specific value when populating the value and not a value that filters with the context, sorry for the confusion as I didn't make it clear that blank value is a calculated column.

        blank value = AVERAGEX(FILTER('sample_data_for_forecast','sample_data_for_forecast'[Month]<8),'sample_data_for_forecast'[SignedValue])

        Additionally you can use Signed value to make judgements, giving different colours to indicate that this is the predicted value.

         

         

         

        If you have any other queries you can look at my uploaded pbix file, I hope it helps you with your queries!

         

         

         

        Hope it helps!

        Best regards,
        Community Support Team_ Tom Shen

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