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

     

     

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.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.