Visualize a complex monthly sumproduct

[menelaos1983]

Hey guys,

I've been trying to figure out a way to solve the following problem on PowerBI. 

I have a list of contracts with client name, start date, end date, daily rate, ie the following format:

Client A | 2017-01-01 | 2017-01-12 | $20,000

Client B | 2017-01-12 | 2018-01-01 | $12,000
Client C | 2016-05-01 | 2016-09-12 | $13,200

I need to create a bar chart showing the total money my company expects to make PER MONTH from the above contract pipeline. What I need in reality is to look 12 months ahead, and for each month to calculate how much money I'll make from each contract, and then show the total sum across all the contracts. In other words, for Jan 2017:

Client A: 12 days * $20,000 = $240,000

Client B: 20 days * $12,000 = $240,000

Client C: 0 days * $13,200 = $0

Total = $480,000

This I need for every month from Jan 2017 until Dec 2017. What confuses me is that in reality I need a sumproduct: sum across all contracts of the value (number of days this contract is active in this particular month * daily rate of the contract). Haven't found a way to do this. Remember that the bar chart will have to be populated for next-12-months, ie. always (dynamically) look 12 months ahead. 

I wrote the following quick-and-dirty alternative which looks at active contracts for each month and assumes all active contracts last for the full month. That's obviously incorrect, but haven't found a way to write the correct sumproduct calculation.

Coverage value = 
SUMx(
	FILTER(
		'Future contracts input sheet',
		and(
			and(
				and(
					'Future contracts input sheet'[Contract start date]<='Vessel budget'[Date],
					'Future contracts input sheet'[Contract end date]>='Vessel budget'[Date]),
				'Vessel budget'[Vessel]='Future contracts input sheet'[Vessel]),
			'Future contracts input sheet'[Type]="Firm")),
'Future contracts input sheet'[Day rate])*31

Any ideas? All help will be hugely appreciated!

Cheers,

menelaos

[v-caliao-msft]

Hi @menelaos1983,

For your sample data

Client A | 2017-01-01 | 2017-01-12 | $20,000

Client B | 2017-01-12 | 2018-01-01 | $12,000
Client C | 2016-05-01 | 2016-09-12 | $13,200

you need the result

Client A: 12 days * $20,000 = $240,000

Client B: 20 days * $12,000 = $240,000

Client C: 0 days * $13,200 = $0

Total = $480,000

I cannot understand why Client A is 12 days, Client B is 20 days and Client C is 0 days. Please elaborate the logic for your calculation, so that we can make further analysis.

Regards,

Charlie Liao

[menelaos1983]

Hi @v-caliao-msft

Sure thing. I'm trying to create a bar chart that shows the value of our jobs pipeline per month, for the next 12 months.

First month is Jan 2017, last is Dec 2017 (it should automatically select the right months, but let's stick to Jan-Dec 2017 for this example).

Now for EACH month we need to calculate the value of the pipeline.

For January:

• Client A will give us $20,000 per day * 12 days (from 1st until 12th of the month)
• Client B will give us $12,000 per day * 20 days (from 12th until 31st of the month)
• Client C will give us nothing (since his contract has already finished in Sep 2016)

For February:

• Client A will give us nothing (his contract finished in Jan 2017)
• Client B will give us 12,000 * 28 days (from 1st to 28th of the month)
• Client C will give us nothing (since his contract has already finished in Sep 2016)

For March:

• Client A will give us nothing (his contract finished in Jan 2017)
• Client B will give us 12,000 * 31 days (from 1st to 31st of the month)
• Client C will give us nothing (since his contract has already finished in Sep 2016)

...etc etc

What I want to visualize is the sum of all contracts per month, ie. a bar chart with 12 bars.

First bar will be Jan 2017, and the value will be 480,000

Second bar will be Feb 2017, and the value will be 336,000

Third bar will be Mar 2017, and the value will be 372,000

...etc

I hope this clarifies. Any ideas?

Many thanks!

Cheers,

menelaos