Goal-Seek Measure to Achieve Target Value

[Anonymous]

Hi,

I have the below line graph with several different data series

• Nomination - which is the target value/target average
• Forecast
• Production - Daily stream of actual volumes

I am wanting to include a measure that determines what you have to produce the remaining time period to achieve the nomination value/average. In the below example, it would look a bit like the solid black line

[ryan_mayu]

@Anonymous

Is that a fixed number or we need to calculate it? If we need to calculate, what's the logic of the calculation?

Measure= if(SELECTEDVALUE('Sheet30'[date])>TODAY(),120,BLANK())

If it's a fixed value, please see if this is helpful. If it's a dynamic value, you can replace the number with your calculation. Thanks

[Anonymous]

@ryan_mayu

This is what I am trying to do in an excel solution 

Date	Nomination	Production	Measure
1/1/2019	34,414.49	27,288.60	27,288.60	Actual
1/2/2019	34,414.49	24,345.00	24,345.00	Actual
1/3/2019	34,414.49	23,687.05	23,687.05	Actual
1/4/2019	34,414.49	25,656.85	25,656.85	Actual
1/5/2019	34,414.49	28,511.20	28,511.20	Actual
1/6/2019	34,414.49	28,614.96	28,614.96	Actual
1/7/2019	34,414.49	30,035.89	30,035.89	Actual
1/8/2019	34,414.49	33,114.09	33,114.09	Actual
1/9/2019	34,414.49	32,630.63	32,630.63	Actual
1/10/2019	34,414.49	33,025.25	33,025.25	Actual
1/11/2019	34,414.49	34,218.17	34,218.17	Actual
1/12/2019	34,414.49	33,495.18	33,495.18	Actual
1/13/2019	34,414.49	32,981.21	32,981.21	Actual
1/14/2019	34,414.49		37,735.84	Predicted
1/15/2019	34,414.49		37,735.84	Predicted
1/16/2019	34,414.49		37,735.84	Predicted
1/17/2019	34,414.49		37,735.84	Predicted
1/18/2019	34,414.49		37,735.84	Predicted
1/19/2019	34,414.49		37,735.84	Predicted
1/20/2019	34,414.49		37,735.84	Predicted
1/21/2019	34,414.49		37,735.84	Predicted
1/22/2019	34,414.49		37,735.84	Predicted
1/23/2019	34,414.49		37,735.84	Predicted
1/24/2019	34,414.49		37,735.84	Predicted
1/25/2019	34,414.49		37,735.84	Predicted
1/26/2019	34,414.49		37,735.84	Predicted
1/27/2019	34,414.49		37,735.84	Predicted
1/28/2019	34,414.49		37,735.84	Predicted
1/29/2019	34,414.49		37,735.84	Predicted
1/30/2019	34,414.49		37,735.84	Predicted
1/31/2019	34,414.49		37,735.84	Predicted
	34,414.49	29,815.70	34,414.49

So the Measure uses the "Production" series up until it terminates, in this case on 1/13 then it seeks the array from 1/14 to 1/31 in order to bring it's average equal to the Noms average (34,414.49)

[ryan_mayu]

@Anonymous

I am trying to understand your request.

So the Nomination is the straight red dot line in your chart. Produciton is the black curve.

How you get your Nomiation? Do you need to do any calucation?

How you get 37735.84?

Do you want to show a straight line which value is 37735.84 from 1/14-1/31?

[Anonymous]

@ryan_mayu

Correct, the nomination is fed in from a data source. It technically a single point because it is provided in monthly format (1/1/2019) but I chart the average line to show it visually.

the 37,735.84 number I calculated in excel with goal-seek, but was hoping to do something similar with a measure in my power BI model. It would be dynamic as the Production array feeds in another new data point every day. 

Essentially it says, we need to produce 37,735.84 units of production on a daily rate through the remained of the month to achieve our target (nomination).

I don't care if it is a straight line from the last day point (1/14 in this situation) through end of time frame or it just shows up as a constant line across the whole x axis.

[ryan_mayu]

@Anonymous

I still can't get how you do the calculation.

Maybe you can try below logic, then if you have a production, it will show production. If not, it will show the result of your calculaiton. Thanks

If ( isblank(production), measure, production)

[Anonymous]

Set Cell D33 = B33 by
Changing cell D15
In this manual approach, i set range D16:D32 = D15 so it accounts for all the remaining days in month

[ryan_mayu]

@Anonymous

You can create a column. Hope this is helpful. Thanks

column = if(ISBLANK(Sheet32[production]),37735.84,Sheet32[production])