cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bhofbaue Frequent Visitor
Frequent Visitor

Help to create an aggregated forecast table

I'm trying to produce a table that will aggregate a forecast by date using data points in an Opportunity Table.

 

Opportunity Table

Opportunity IDEstimated ValueOpportunity DateOpportunity TypeStart of ForecastEnd of Forecast
1$15,00010/31/2019455 Day Forecast10/31/201801/29/2020
2$40,00005/01/2019455 Day Forecast05/01/2018

07/30/2019

3$10,00007/01/2019455 Day Forecast07/01/201809/29/2019
4$20,00009/05/201935 Day Forecast09/05/201810/10/2019

 

Forecast Distribution Table

Days from Opp Date455 Day Forecast: % of Total Estimated Value (not cumulative)35 Day Forecast: % of Total Estimated Value (not cumulative)
...2.244%null
32.598%null
23.305%null
1 (days before)4.973%null
0 (opp date)3.094%1%
-1 (days after)0.984%2%
-20.550%2%
-30.294%5%
...  

 

DESIRED OUTCOME

For each Opportunity, I want to multiply the estimated value by the % in the appropriate forecast table column across all "Days From Opp Date". I want to aggregate a sum of all results in a table as shown below. I only need the forecast table to proceed into the future, so performing the calculations for dates in the past is unnecessary.

 

I'll be using slicers of the Opportunities on the page, so I will want this table to be dynamically related to the slicers, only passing data into the forecast table that applies to the slicers that have been selected.

 

Forecast

DateForecast
today's dateSum of Forecast for Opportunities for this Date
today's date + 1Sum of Forecast for Opportunities for this Date
today's date + 2Sum of Forecast for Opportunities for this Date
today's date + 3Sum of Forecast for Opportunities for this Date
......
today's date + 455Sum of Forecast for Opportunities for this Date
2 REPLIES 2
Community Support Team
Community Support Team

Re: Help to create an aggregated forecast table

Hi @bhofbaue ,

 

1.Would you like to create a calculated column or measure to calculate [Forecast]=[Estimated Value] *[455 Day Forecast: % of Total Estimated Value (not cumulative)] or calculate [Forecast]=[Estimated Value] *[35 Day Forecast: % of Total Estimated Value (not cumulative)] , based on the [Opportunity Type] and [Days from Opp Date] ?

 

2.Is the [Date] to show the date between [Start of Forecast] and [End of Forecast]?

 

3.Which field would you like to put onto Slicer visual?

 

I am not sure what desired result would you want, could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Best Regards,

Amy

 

 

bhofbaue Frequent Visitor
Frequent Visitor

Re: Help to create an aggregated forecast table

Hi v-xicai. Thank you for the reply!

 

Either a calculated column or measure would be fine. I had anticipated that I would need a calculated column rather than a measure, but I'm open to either.

 

I want the [Opportunity Type] to determine which column/field to use (either [455 Day] or [35 Day]) for the calculation/measure.

 

The [Date] is the first column in the the Forecast table. If a date between the [Start of Forecast] and [End of Forecast] is equal to [Date], then that Opportunity's [Estimated Value] should be included in the calculation of the [Forecast]. The [Date] is today's date + the total number of days in to the future that a forecast could possibly reach (455). 455 may seem arbitrary, but our revenue cycles almost never last more than 90 days after an event, and they almost never begin more than 365 days before an event. Therefore, the total number of days that a forecast would exist would be 455.

 

There are tables and fields not listed here that I will plan to slice by: Date (from my Date Table), Event Type, Opportunity Owner, and possibly Event Date.

 

Right now my pbix file is filled with sensitive data, so I can't share it without a serious amount of cleansing. I can try to create a few sample tables and connect them, but it may take me a few days to prep that. Let me know if it is necessary. Otherwise, if you can provide 1 or 2 possible solutions, I'd be willing to give them a shot and see how far I get and respond with the results.

 

Thank you so much! Very eager to solve this problem!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 246 members 2,507 guests
Please welcome our newest community members: