How to divide values across days/weeks/months and then sum by year?
I'd like to ask your help in a task I'm trying to complete. I've tried several methods but can't quite get the result I was hoping for.
I'm tracking agreements with varying validity periods and $ values. I'd like to take the total value of a contract, divide it across the validity period and then sum the $ values by year. The assumption is that any $ value in the past (including today) will be considered actual and anything in the future will be forecast. Below is a screenshot of what data looks like:
I'm trying to build a sheet that will take the data above and sum the $ values per year, with $ values of agreements spanning multiple years being divided equally. The $ values in the current year will be split between actuals and forecast as the days move along.
Finally, the data would be presented in a dashboard chart with a date range covering the current year, the year before and after and any other previous/future periods grouped together. The years on the X-axis will be dynamic and change every year. The current year will show both actuals and forecast and will also change as the days progress. Below is a screenshot of what the chart will look like.
(Please note that the data in the first screenshot do not match the chart. They are just representations of what the data and chart might look like.)
Hope you could share your inputs.