I am working with Beyond Budgeting and would like to showcase a rolling four-quarters total of revenue, shown for each quarter in the past and future, and combining actuals and forecast. I can’t wrap my mind around it. Any brilliant ideas on how to accomplish this?
Actuals on a day-to-day basis (from the ERP).
Forecast from an Excel-sheet in quarters (right now for Q1 - Q4 2019 and Q1 2020). Each quarter a new quarter will be added.
The two are linked by a table with Project numbers and by a table with Dates.
A measure which can be put into a bar chart. The bar chart should show quarters on the x-axis and revenue (for four quarters) on the y-axis.
Q4 2018 should calculate revenue for Q1 2018 - Q4 2018, all actuals.
Q1 2019 --> Q2 2018 - Q1 2019, all actuals.
Q2 2019 --> Q3 2018 - Q1 2019, plus the forecast for Q2 2019, as this is not finished yet.
Q3 2019 --> Q4 2018 - Q1 2019, plus estimate for Q2 - Q3 2019.
And this should go on until Q1 2020, which will be all estimates and the last quarter we have estimates for. By June, right before Q3 2019 begins, we will add Q2 2020 to the estimate sheet, and the measure should add this to the graph.
Seems a bit complicated to me, since the measure needs to take into account which quarters are fully done (Q1 2019) by Today and which ones need to add on estimates to collectively become four full quarters.
Brilliant people of Enterprise, please chip in