Creating Forecast based on Pipeline Values between Dates and conversion rate

[Heiner26]

Hey everyone,

first of all: great support you give to the community 👏 encourages me to keep on discovering the potential of Power BI.

Was searching this forum for a solution for the challenge i face - but as I could not find I dare to open a new topic:

I want to show the potential value of a project in the future in a kind of forecast.

I have already mapped this successfully once in Excel. This is what my data table looks like:

Project Name	Phase	Status	DatePhase1	DatePhase2	DatePhase3	DatePhase4	Value
A	1	live	01.08.2023	14.04.2024	25.09.2024	03.06.2025	150
B	2	rejected	15.09.2023	15.04.2024	01.10.2024	05.07.2025	80
C	3	live	23.09.2023	18.06.2024	18.10.2024	16.09.2025	200
D	2	live	14.10.2023	22.07.2024	20.12.2024	13.08.2025	50
E	4	live	17.03.2025	14.08.2025	16.09.2024	20.10.2024	80

- Each project has an initial value at the start
- Each project goes through 4 phases until the end
- Each project has the status “live” at the beginning, if it is stopped it gets the status “rejected”
- Only projects from phase 2 and with the status “live” are taken into account for the forecast
- The date values for entering the respective phase are either already fixed or are calculated based on empirical values
- With each transition to the next phase, the value of the project is reduced to take into account a kind of “conversion rate”
(entering phase 3: 75% of the initial value, entering phase 4: 35%)

- If the project has already reached the phase for which the forecast is to be calculated, 100% of the value is assumed (no conversion rate); if the current phase is lower, the conversion rate is used.

And this is my forecast (desired outcome) based on the pipline values:

	2024
	01	02	03	04	05	06	07	08	09	10	11	12
Phase 2	0	0	0	150	150	350	400	400	250	50	50	0
Phase 3	0	0	0	0	0	0	0	0	80	200	200	238
Phase 4	0	0	0	0	0	0	0	0	0	80	80	80

The line below the year is the month and the last day of the month is used for the comparison.

In Excel, you can use the SUMPRODUCT function to query the individual criteria. Here is an example of how I can calculate the performance for phase 4 (L$4 relates ts the last day of the month in the row below the year):

=SUMMENPRODUKT((FunctionTable[[Value]:[Value]])*(FunctionTable[[DatePhase4]:[DatePhase4]]<>"")*(FunctionTable[[Status]:[Status]]="live")*(FunctionTable[[DatePhase4]:[DatePhase4]]<=L$4)*(FunctionTable[[Phase]:[Phase]]=4))+

SUMMENPRODUKT((FunctionTable[[Value]:[Value]])*(FunctionTable[[DatePhase4]:[DatePhase4]]<>"")*(FunctionTable[[Status]:[Status]]="live")*(FunctionTable[[DatePhase4]:[DatePhase4]]<=L$4)*(FunctionTable[[Phase]:[Phase]]=3))*0,75+

SUMMENPRODUKT((FunctionTable[[Value]:[Value]])*(FunctionTable[[DatePhase4]:[DatePhase4]]<>"")*(FunctionTable[[Status]:[Status]]="live")*(FunctionTable[[DatePhase4]:[DatePhase4]]<=L$4)*(FunctionTable[[Phase]:[Phase]]=2))*0,35

I have already successfully imported the function table into Power BI and also created a date table. I am currently stuck with the calculation of the forecast per phase and date/month...

Is there a chance to build this query or the table for the forecast in Power BI?

I am happy about any advice you can give me ☺️

[Heiner26]

Hey there,

I studied some other threads with similar background and I guess SUMX/CALCULATE together with FILTER could be the solution. I added the following measure to my date table (without taking the phase into account for conversion rate):

ForecastPhase2 = Sumx( FunctionTable, CALCULATE( SUM(FunctionTable [ Value]),

FILTER( FunctionTable, FunctionTable [DatePhase2] <= Min('Datetable'[Date]) && FunctionTable [DatePhase3] >= max('Datetable'[Date])

but the measure is not even shown as column in my date table 😢

maybe power bi is a size too big and i'd better keep using excel 😬

[Heiner26]

Hey @lbendlin my I kindly come back to this topic? Have you been able to find a solution 🥺 or are you still missing some information from my side? Thx and regards

[lbendlin]

sorry, real life got in the way.  Maybe over the weekend.

[lbendlin]

You seem to want this by month yet the DatePhase dates are distributed across all days. Do you want a wighted/partial attribution, or should the full value be attributed to the month regardless of when in the month the phase starts?

The dates on Project E are out of sequence.

[Heiner26]

Hey,

Yes, exactly: by months... So how do inflows and outflows develop for each phase over the respective months! A later aggregation to quarters would also be good - but is not absolutely necessary.

And the value determined via the conversion rate should be fully attributed to the Momat regardless of when in the month the phase was reached. The last day of the month is the deadline, so to speak 🙏

And yes: Project E is out of sequence - was a typo when creating the table....

[lbendlin]

Still waiting for Project E correction.

[Heiner26]

sure - here you go:

Data Table:

Project Name	Phase	Status	DatePhase1	DatePhase2	DatePhase3	DatePhase4	Value
A	1	live	01.08.2023	14.04.2024	25.09.2024	03.06.2025	150
B	2	rejected	15.09.2023	15.04.2024	01.10.2024	05.07.2025	80
C	3	live	23.09.2023	18.06.2024	18.10.2024	16.09.2025	200
D	2	live	14.10.2023	22.07.2024	20.12.2024	13.08.2025	50
E	4	live	17.01.2023	05.03.2024	17.06.2024	31.12.2024	80

desired outcome (forecast for each phase based on current phase and enter dates):

	2024
	01	02	03	04	05	06	07	08	09	10	11	12
Phase 2	0	0	80	230	230	350	400	400	250	50	50	0
Phase 3	0	0	0	0	0	80	80	80	80	280	280	238
Phase 4	0	0	0	0	0	0	0	0	0	0	0	80

[Ashish_Mathur]

Hi,

I am not sure how much i can help but i would like to try.  Share the download link of an MS Excel file with your sumproduct() formula already written there.  I will try to translate that formula into the DAX formula language.

[Heiner26]

Thanks for jumping in, Ashish 🙏

you can download the Excel here: https://www.dropbox.com/scl/fi/0w0w6o5ooa76z005tbwa6/Project_Forecast_DEMO_v2.xlsx?rlkey=7cfjx3pqwh9...

Best Regards,

Heiner

[Ashish_Mathur]

This is my abortive attempt.  I have not been able to get the total - just the count of rows in each month which satisfy all conditions.  Hope this helps marginally.

[Heiner26]

thanks, Ashish!

I appreciate your support very much and for sure this will help me on the way to the soultion 😉

Will have a closer look during this week and I hope its fine to come back to you if there is any questions 🙏☺️

[lbendlin]

What should happen when multiple project phases fall into the same month (or quarter) ?

[Heiner26]

when multiple project phases fall into the same month (or quarter) the value - according to the conversion rate - should be shown in pipe for each phase.

eg. a Project is in phase 2 and the calcualted date to be in phase 3 is on 01.03.25 and to be in phase 4 on 30.03.25. Then for March 25 75% of value should be shown in pipe of phase 3 and also for March 25 35% of value should be shown in pipe of phase 4 ☺️