cancel
Showing results for
Did you mean:
Frequent Visitor

## Services revenue by month from CRM sales forecasts

I have sales forecast data for upcoming projects in the following format:

 Project Revenue Start Date End Date A \$                    16,000 11/1/2016 12/30/2016 B \$                    20,000 12/5/2016 3/31/2017 C \$                    10,000 11/15/2016 12/30/2016 D \$                    12,000 1/2/2017 3/31/2017

I'm looking for the best way in PowerBI to map this to expected revenue each month. Basic assumption would be that the revenue received will be constant over time (so if a project is for \$100,000 and extends over 5 months, each month should show \$20,000) - this is imperfect vs. reality but "good enough" for my business purpose.

I have an existing Excel template that does this with some rather nasty formulas in a grid so it looks like this:

 Period 1 2 3 Start 1/1/2017 2/1/2017 3/1/2017 End 1/31/2017 2/28/2017 3/31/2017 Project Start End Revenue 1 2 3 A 11/1/2016 12/30/2016 \$           1,000 \$                         - \$                         - \$                         - B 12/5/2016 3/31/2017 \$         12,500 \$                   3,312 \$                   2,991 \$                   3,312 C 11/15/2016 12/30/2016 \$           1,400 \$                         - \$                         - \$                         - D 1/2/2017 3/31/2017 \$           6,000 \$                   2,022 \$                   1,888 \$                   2,090

The formula in each revenue cell above is basically:

=(MAX(MIN([project end],[period end])-MAX(\$[project start],[period end])+1,0))*\$[Revenue \$ / day]

So I'm looking for the best (easier?) way to do this kind of report in PowerBI.  Preferably then going beyond my table above into some actual bar graphs / etc.

Suggestions / pointers appreciated!

-Rob

3 REPLIES 3
Moderator

## Re: Services revenue by month from CRM sales forecasts

Hi @rneppell,

From the Excel formula you provided, it seems that [Revenue \$ / day] is a column, but from your sample data, I can't find this column. In your scenario, I would suggest you clarify the formula how to calculate and get values in columns 1,2,3. It would be better if you could share the exact sample data and expected results for our analysis.

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Services revenue by month from CRM sales forecasts

Apologies; I was trying to simplify & remove extraneous info, but went a bit too far. Let's try again; here's the example:

And here's the actual .xlsx file: https://1drv.ms/x/s!AvkHe_7uKU1SgdJIYBe59136yEPeeg

So the formula in cell G7 which calculates the revenue for Project B over the period 1/1/2017 - 1/31/2017 for example is:

=(MAX(MIN(\$C7,G\$3)-MAX(\$B7,G\$2)+1,0))*\$F7

I assume I could learn how to directly replicate this method in PowerBI --- but what I'm really interested in is if there is a *better* way to create this kind of aggregated forecast across time periods.

Rob

Frequent Visitor

## Re: Services revenue by month from CRM sales forecasts

...no thoughts at all, folks?

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!