Forum Discussion

RGG's avatar
RGG
Frequent Visitor
7 years ago
Solved

Monthly Revenue Forecast Between Two Dates

Hi!  I am trying to build a monthly revenue forecast where I have a list of opportunities, the expected monthly value of each opportunity, the expected start date of the project, and the expected end date of the project.  

 

I'm trying to convert that data into a waterfall chart that shows when revenue hits/falls off by project, where the table is dynamic to grow as the sales data (above) is updated.  In other words, as new opportunities are added to the above dataset, the chart (below) automatically updates.

 

As a new user to PowerBI, I'm sure there is a basic function that I just don't know about!  Thank you!

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi RGG ,

     

    Measure 3 = IF(ISINSCOPE('Table'[OPPORTUNITY]),CALCULATE(SUMX('CALENDAR',[Measure])),SUMX(ALL('Table'[OPPORTUNITY]),CALCULATE(SUMX('CALENDAR',[Measure])))
)

     

8 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi RGG ,

     

    Here I created a sample for your reference, please check the following steps as below.

    1. Create a calendar table as below and create relationship between tables.

     

    CALENDAR = FILTER(CALENDARAUTO(),DAY([Date])=1)

    2. To achieve our goal by a measure.

    Measure = 
VAR std =
    CALCULATE (
        MAX ( 'Table'[START] ),
        ALLEXCEPT ( 'Table', 'Table'[OPPORTUNITY] )
    )
VAR endte =
    CALCULATE ( MAX ( 'Table'[END] ), ALLEXCEPT ( 'Table', 'Table'[OPPORTUNITY] ) )
VAR datecal =
    MAX ( 'CALENDAR'[Date] )
RETURN
    IF (
        std <= datecal
            && endte >= datecal,
        CALCULATE (
            SUM ( 'Table'[MONTHLY REVENUE] ),
            ALLEXCEPT ( 'Table', 'Table'[OPPORTUNITY] )
        ),
        BLANK ()
    )

     

    Pbix as attached.

     

    • RGG's avatar
      RGG
      Frequent Visitor
      7 years ago

      This is fantastic.  Is there a reason why your matrix isn't summing the values by year?  For example, opportunity AAA in your example file should sum to $70K in 2019.