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!
Solved! Go to Solution.
I have the same question! This may not help you, but I've calculated the following:
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?
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.