cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 169 members 1,940 guests
Please welcome our newest community members: