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 fa...
Need Help
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....

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Still waiting for Project E correction.

  • Heiner26's avatar
    Heiner26
    Frequent Visitor
    2 years ago

    sure - here you go:

    Data Table:

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

     

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

     2024
     010203040506070809101112
    Phase 2008023023035040040025050500
    Phase 30000080808080280280238
    Phase 40000000000080
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      2 years ago

      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.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

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

      • Heiner26's avatar
        Heiner26
        Frequent Visitor
        2 years ago

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