Spread column value (revenue) over years based on start & end date columns of project table

[Anonymous]

Hello,

In my projects table I have a CreationDate, Startdate, Enddate and a revenue. What I would like to achieve is create calculated columns for Revenue Current Year, Revenue Next Year, Revenue Y+2, for projects that run longer then a year so I can visualize in which year revenues from projects will be expected. Current Year should be YEAR(CreationDate).  Revenue should be divided over the years directly proportional to the number of project days of the total duration of te project in each year.

If there is a better way instead of using calculated columns for calculating revenues per calendar year based on this data I'd be happy to know!

I started out with first calculating the datediff in days between startdate and enddate but now I am stuck. Any help is much appreciated!

[Ashish_Mathur]

Hi,

See if my post here helps - Distribute projected revenue annually.

[barbforsman]

I have the same question!  This may not help you, but I've calculated the following:

1. Number of months remaining
2. Number of months in the current year
3. Number of months in the year the contract expires
4. Number of months in between start and end years
5. MRC per month remaining on the contract

So I can calculate MRC in the current year and end year, and the total MRC in the years between, but I'm at a loss how to display this in one nice chart.  I have completed this in excel so I know the expected results.  The expected results are displayed as an image in my file.

I've uploaded my pbix file here.

Any suggestions on how to display the contract MRC per year?

[barbforsman · Accepted solution]

I have plowed my way through a very manual way of doing this, but it calculates the correct MRC I am expecting for each year the contract is in place.  I've updated the .pbix file here.  My only issue now is how to show this in a graph that looks like this!

I covet any suggestions!

[Anonymous]

I think you solved it 😊 Going to look at your code asap. Can you not visualize it with a waterfall chart easily?

[barbforsman]

I'm not seeing the numbers I expect to see on the waterfall chart.  I was thinking my 2018 in the chart would equal the MRC Yr+0 in the matrix above.  But what I don't know is a LOT, so maybe I just don't understand waterfall charts enough?
I just updated the .pbix file again with the waterfall chart included.