Help to create an aggregated forecast table

[Anonymous]

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

Opportunity Table

Opportunity ID	Estimated Value	Opportunity Date	Opportunity Type	Start of Forecast	End of Forecast
1	$15,000	10/31/2019	455 Day Forecast	10/31/2018	01/29/2020
2	$40,000	05/01/2019	455 Day Forecast	05/01/2018	07/30/2019
3	$10,000	07/01/2019	455 Day Forecast	07/01/2018	09/29/2019
4	$20,000	09/05/2019	35 Day Forecast	09/05/2018	10/10/2019

Forecast Distribution Table

Days from Opp Date	455 Day Forecast: % of Total Estimated Value (not cumulative)	35 Day Forecast: % of Total Estimated Value (not cumulative)
...	2.244%	null
3	2.598%	null
2	3.305%	null
1 (days before)	4.973%	null
0 (opp date)	3.094%	1%
-1 (days after)	0.984%	2%
-2	0.550%	2%
-3	0.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

Date	Forecast
today's date	Sum of Forecast for Opportunities for this Date
today's date + 1	Sum of Forecast for Opportunities for this Date
today's date + 2	Sum of Forecast for Opportunities for this Date
today's date + 3	Sum of Forecast for Opportunities for this Date
...	...
today's date + 455	Sum of Forecast for Opportunities for this Date

[v-xicai]

Hi @Anonymous ,

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

[Anonymous]

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!