Creating a table of projected revenue

[Anonymous]

Hello, I am working on a sales dashboard that will project out future revenue based on a table of projects. I am new to PowerBI so I wanted to get the community's input on how to approach this.  Here's how our data is current set up.

Our sales fact table, for example:

Project	Amount	Length	Start Date
Project 1	100	2	4/1/2020
Project 2	250	3	1/15/2019
Project 3	50	1	6/17/2021

Then we have a table of "burn rates". Each project has a different length so there is a set of rates for each project length (rounded to the nearest year)

Length	Q1 Y1	Q2 Y1	Q3 Y1	Q4 Y1	Q1 Y2	Q2 Y2	Q3 Y2	Q4 Y2	Q1 Y3	Q2 Y3	Q3 Y3	Q4 Y3
1	0.3	0.3	0.2	0.2
2	0.15	0.15	0.1	0.1	0.15	0.15	0.1	0.1
3	0.1	0.1	0.06	0.06	0.1	0.1	0.06	0.06	0.1	0.1	0.06	0.06

I am trying to create a new table that multiplies the project amount by the correct burn rate, based on the project length. I was thinking this might be a job for a calculated table, but again, I'm new to PBI so not sure.

Project	Q1 Y1	Q2 Y1	Q3 Y1	Q4 Y1	Q1 Y2	Q2 Y2	Q3 Y2	Q4 Y2	Q1 Y3	Q2 Y3	Q3 Y3	Q4 Y4
Project 1	15	15	10	10	15	15	10	10	0	0	0	0
Project 2	25	25	15	15	25	25	15	15	25	25	15	15
Project 3	15	15	10	10	0	0	0	0	0	0	0	0

The final product would also account for the start date of the project, so we can see the total projected per quarter/year. We have a date table related to the sales fact table, so I was hoping this could be solved through the correct relationships in the data model.

	2019	2020	2021
	Q1 Y1	Q2 Y1	Q3 Y1	Q4 Y1	Q1 Y2	Q2 Y2	Q3 Y2	Q4 Y2	Q1 Y3	Q2 Y3	Q3 Y3	Q4 Y4
Project 1	0	0	0	0	0	15	15	10	10	15	15	10
Project 2	25	25	15	15	25	25	15	15	25	25	15	15
Project 3	0	0	0	0	0	0	0	0	0	0	15	15
Total	25	25	15	15	25	40	30	25	35	40	45	40

I appreciate any tips. Thank you!

[v-xicai]

Hi @Anonymous ,

>>I am trying to create a new table that multiplies the project amount by the correct burn rate, based on the project length.

You can create measure like DAX below.

Measure1=
var Amount= CALCULATE(SUM('sales fact'[sale]),FILTER(ALLSELECTED('sales fact'), 'sales fact'[Project] =MAX('sales fact'[Project])&&'sales fact'[Length] =MAX('sales fact'[Length])&&YEAR('sales fact'[Date]) =YEAR(MAX('sales fact'[Date]))&&ROUNDUP(MONTH('sales fact'[Date])/3,0)=ROUNDUP(MONTH(MAX('sales fact'[Date]))/3,0)))
var Rate= CALCULATE(MAX('burn rates'[Rate]),FILTER(ALLSELECTED('burn rates'), 'burn rates'[Length] =MAX('burn rates'[Length])&&YEAR('burn rates'[Date]) =YEAR(MAX('burn rates'[Date]))&&ROUNDUP(MONTH('burn rates'[Date])/3,0) =ROUNDUP(MONTH(MAX('burn rates'[Date]))/3,0)))
return
Amount*Rate

If I misunderstood it, could you please share your sample data or desired output screenshots for further analysis, you can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xicai, Thank you for your suggestion. I wasn't able to test out the measure you shared because it doesn't quite match up how my data is modeled. The burn rate table doesn't have a single date, but rather a list of project lengths in years, and then a corresponding rate for each quarter. I created a sample pbix file with the same data model and am hoping that you can take a look.

sample project revenue report.pbix

Here is an example of what the final product should look like (not exactly the right amounts, but basically showing the labor costs * burn rate (based on period of performance start date) for each quarter that the project will occur. 

Proposal Name	Labor Costs	Probability (%)	Period of Performance Start	Period of Performance End	Project Duration (months)	Q1 2019	Q2 2019	Q3 2019	Q4 2019	Q1 2020	Q2 2020	Q3 2020	Q4 2020	Q1 2021	Q2 2021	Q3 2021	Q4 2021
Proposal-00494	$          199,225	40	9/1/2019	9/1/2021	24			$      39,844.96	$      39,844.96	$     39,844.96	$     39,844.96	$     39,844.96	$     39,844.96	$     59,767.44	$     59,767.44	$     39,844.96	$     39,844.96
Proposal-00495	$       3,046,073	40	10/1/2020	10/1/2025	60								$  609,214.60	$  913,821.90	$  913,821.90	$  609,214.60	$  609,214.60
Proposal-00485	$       2,060,205	50	11/29/2019	11/29/2022	36			$   412,041.00	$   412,041.00	$  618,061.50	$  618,061.50	$  412,041.00	$  412,041.00	$  618,061.50	$  618,061.50	$  412,041.00	$  412,041.00
Proposal-00045	$          299,107	80
Proposal-00473	$          965,660	50	1/2/2019	8/2/2021	31	$   289,698.00	$   289,698.00	$   193,132.00	$   193,132.00	$  289,698.00	$  289,698.00	$  193,132.00	$  193,132.00	$  289,698.00	$  289,698.00	$  193,132.00
Proposal-00466	$       1,016,207	50
Proposal-00247	$       2,598,215	40	10/31/2019	2/29/2020	4			$   519,643.00	$   519,643.00	$  779,464.50	$  779,464.50	$  519,643.00
Proposal-00230	$       3,260,558	80	1/1/2020	5/1/2024	52			$   652,111.52	$   652,111.52	$  978,167.29	$  978,167.29	$  652,111.52	$  652,111.52	$  978,167.29	$  978,167.29	$  652,111.52	$  652,111.52
Proposal-00432	$             75,445	80
Proposal-00482	$       1,409,580	50
Proposal-00441	$          230,967	40	4/1/2019	10/1/2020	18		$      69,290.10	$      46,193.40	$      46,193.40	$     69,290.10	$     69,290.10	$     46,193.40	$     46,193.40
Proposal-00405	$       1,366,085	40	1/25/2019	7/25/2022	42	$   409,825.50	$   409,825.50	$   273,217.00	$   273,217.00	$  409,825.50	$  409,825.50	$  273,217.00	$  273,217.00	$  409,825.50	$  409,825.50	$  273,217.00	$  273,217.00
Proposal-00496	$       1,009,179	40	9/1/2019	8/1/2021	23		$   302,753.70	$   201,835.80	$   201,835.80	$  302,753.70	$  302,753.70	$  201,835.80	$  201,835.80	$  302,753.70	$  302,753.70	$  201,835.80	$  201,835.80
Proposal-00493	$          407,484	40	12/2/2019	12/2/2021	24				$      81,496.80	$  122,245.20	$  122,245.20	$     81,496.80	$     81,496.80	$  122,245.20	$  122,245.20	$     81,496.80	$     81,496.80
Proposal-00498	$       2,469,093	40	8/1/2019	8/1/2022	36				$   493,818.60	$  740,727.90	$  740,727.90	$  493,818.60	$  493,818.60	$  740,727.90	$  740,727.90	$  493,818.60	$  493,818.60
Proposal-00488	$          261,048	90
Proposal-00492	$          915,927	40
Proposal-00490	$             18,094	40

[Anonymous · Accepted solution]

For anyone who's interested, I ended up with a solution that seems to be working. I found this post https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/ from @AlbertoFerrari that got me pointed in the right direction. 

My sales fact table has a start and end date that corresponds with the period over which revenue needs to be calculated. I used that to generate a new table that has a row for every day the project is active. Then I created a measure to give me the amount per day which can then be summarized by month, quarter, year, etc.

The tricky part was applying a burn rate that varies over time since we assume that revenue will not be spread uniformly across the entire project but instead ramp up, slow down, and then ramp back up. I assume that the first 1/4 of the project will result in 30% of the revenue, the second and third 1/4's will each result in 20%, and the final 1/4 will result in 30%.

To achieve this I ranked each day of the project and then divide by the total number of days in the project. Then I created a burn rate measure based on that percentage that says: If % of total is less than 25%, burn rate is x%, if less than 75%, burn rate is x%, etc.

Then I can sumx my daily amount by my daily burn rate and the total will be correct whether the project is 5 days or 500 days. The final product shows projected revenue by date and can be drilled up/down from year to day. 

I suspect that some of my measures are less than ideal but it seems to be working. If anyone has dealt with a similar problem and wants to chat let me know!