Monthly Revenue Forecast Between Two Dates

[RGG]

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!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-frfei-msft]

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]

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.

[v-frfei-msft]

Hi @RGG ,

To create another measure based on [measure].

Measure 2 = SUMX('CALENDAR',[Measure])

[v-frfei-msft · Accepted solution]

Hi @RGG ,

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

[RGG]

Thank you for the update.  The solution works for each line, but the sum for each column is incorrect.  How do I get each column to also add up properly?

[Ashish_Mathur]

Hi,

I do not know whom you are replying to.  Have you tried my solution?

[RGG]

Thanks @Ashish_Mathur .  I was responding to @v-frfei-msft's last update. 

I appreciate your approach, but my data set is configured differently than how your solution attacked the problem.  Your dataset had each opportunity with multiple revenue lines, each with its own date.  My data set has each opportunity once, each with a start/end date, and a monthly revenue. I've got a 90% solution, and the last gap is that each column does not add properly.  I notice that @v-frfei-msft's last update likewise had the issue where each column does not add correctly, and was looking to see if there was a setting/configuration that can correct this problem.