Services revenue by month from CRM sales forecasts

[rneppell]

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

[v-qiuyu-msft]

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

[rneppell]

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]

...no thoughts at all, folks?