cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rneppell Frequent Visitor
Frequent Visitor

Services revenue by month from CRM sales forecasts

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

  

ProjectRevenueStart DateEnd Date
A $                    16,00011/1/201612/30/2016
B $                    20,00012/5/20163/31/2017
C $                    10,00011/15/201612/30/2016
D $                    12,0001/2/20173/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:

 

   Period123
   Start1/1/20172/1/20173/1/2017
   End1/31/20172/28/20173/31/2017
       
ProjectStartEndRevenue 123
A11/1/201612/30/2016 $           1,000 $                         -   $                         -   $                         -  
B12/5/20163/31/2017 $         12,500 $                   3,312 $                   2,991 $                   3,312
C11/15/201612/30/2016 $           1,400 $                         -   $                         -   $                         -  
D1/2/20173/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 v-qiuyu-msft
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.
rneppell Frequent Visitor
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. 

 

Thanks in advance,

 

Rob

 

 

rneppell Frequent Visitor
Frequent Visitor

Re: Services revenue by month from CRM sales forecasts

...no thoughts at all, folks?

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors