Filling Missing Forecast Numbers in Matrix Analysis

[mehlenbae2]

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?

[Anonymous]

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]

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]

Hi @mehlenbae2 ，

Has your problem been solved after all this time, or has a new problem arisen, if there are any other questions on this issue, feel free to contact me and I'll get back to you as soon as I receive the message.

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.

[Anonymous · Accepted solution]

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.