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.