Forum Discussion
Calculate dynamic forecast using historical data
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:
- Define variables
- Build the table virtually
- 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.
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])
2 Replies
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.
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])
