Calculate dynamic forecast using historical data

[Anonymous]

Hello,

Please excuse me for summarizing and using improper formating in this message - I'm new to this!

(This is not about the forecast feature built-into Power BI visualizations)

I'm trying to determine how to create a forecast measure that is built fundamentally like this:

forecast ($) = opportunity total ($) x close % x age distribution %

Opportunity total is an easy sum measure.

Close % is an easy divide calculation.

Age Distribution is a table, and this is where I get out of my experience. In excel, this is easy using sumproducts, but it's static.

Age Distribution is a table that looks like this for 18 months:

*Note that the month would be number of months back from today (or current selected). That is, a forecast for March is a piece of each month behind it.

Month	Age%
1	.20
2	.15
3	.11
4	.08
5	.06
6	.04

I would like to be able to virtually calculate the following table:

*See note above - months may be considered negative numbers

Month	Age%	Close%	Opp Total	Forecasted Sales
1	0.2	0.3	$1,000	$60
2	0.15	0.3	$800	$36
3	0.11	0.3	$1,100	$36
4	0.08	0.3	$900	$22
5	0.06	0.3	$750	$14
6	0.04	0.3	$1,300	$16
			Total	$183

The "total" shown here would be the result of the measure. The table above would be virtual. I believe I'd have to:

1. Define variables
2. Build the table virtually
3. SUMX calculations for each row (forecast = close% x opp total x age %)

I'm trying to provide enough detail for help, but not so much that I drown out the core of my question. Please let me know if I can provide more detail.

Thank you all so much! This forum has been very instructive.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Kindly try below measure:

I use the [measureclose]and [measureopp] to represent the measure Close % and Opportunity total

Forecasted Sales = [Measureclose]*[MeasureOpp]*MAX('Table (3)'[Age%])

 Then 

SUMXForecast = SUMX(VALUES('Table (3)'[Month]),[Forecasted Sales])

[parry2k]

@Anonymous try the following measure

Forecast = 
SUMX ( 
VALUE ( MonthTable[Months] ), 
CALCULATE ( MIN ( Table[Age %] ) * CALCULATE ( MIN ( Table[Close %] ) * [Oppy Total Measure] 
)

Would appreciate Kudos 🙂 if my solution helped.