cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
geskes Frequent Visitor
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
barbforsman Regular Visitor
Regular Visitor

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

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!Expected Rev.JPG

 

I covet any suggestions!

5 REPLIES 5
barbforsman Regular Visitor
Regular Visitor

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

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 Regular Visitor
Regular Visitor

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

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!Expected Rev.JPG

 

I covet any suggestions!

geskes Frequent Visitor
Frequent Visitor

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

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

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

 

Expected Rev.JPG

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.

Super User
Super User

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

Hi,

 

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