Forum Discussion

Heiner26's avatar
Heiner26
Frequent Visitor
2 years ago

Creating Forecast based on Pipeline Values between Dates and conversion rate

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 NamePhaseStatusDatePhase1DatePhase2DatePhase3DatePhase4Value
A1live01.08.202314.04.202425.09.202403.06.2025150
B2rejected15.09.202315.04.202401.10.202405.07.202580
C3live23.09.202318.06.202418.10.202416.09.2025200
D2live14.10.202322.07.202420.12.202413.08.202550
E4live17.03.202514.08.202516.09.202420.10.202480


- 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
 010203040506070809101112
Phase 200015015035040040025050500
Phase 30000000080200200238
Phase 4000000000808080

 

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 ☺️

Need Help

13 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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's avatar
      Heiner26
      Frequent Visitor
      2 years ago

      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....

  • Heiner26's avatar
    Heiner26
    Frequent Visitor
    2 years ago

    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's avatar
      lbendlin
      Super User
      2 years ago

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

  • Heiner26's avatar
    Heiner26
    Frequent Visitor
    2 years ago

    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 😬