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?
Data:
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.
Expected output:
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.
I’ve covered all the techniques you’ll need here quite extensively.
Budgeting can be a little more difficult to execute in Power BI because you need to get the model setup right as well as the DAX functions.
It usually takes a few steps to get this working seamlessly.
I’m going to add some key videos below here. But there is also an entire module around the many budgeting technique you can use.
I suggest building this up piece by piece. Get the model right, then get some simple DAX functions working, then move onto some of the more advanced calculation right at the end…this is the only way to do this right.
Hi
I will try to chip in, since this is something I have solved several times for customers.
As you mention yourself, you need a measure that can handle the mix of actuals and forecast depending on the period.
If you choose to make a manual filter selection for the last final period, you can do something like this:
Act+FCST =
VAR Last_Final_Month = CALCULATE(SELECTEDVALUE('End of Month Table'[End of month]))
VAR Actuals = Calculate([SumAmount 1000s];KEEPFILTERS('Date'[Date] <= Last_Final_Month))
VAR Forecast = Calculate([SumForecast];KEEPFILTERS('Date'[Date] > Last_Final_Month))
RETURN
Forecast + Actuals
In this case I have created a second Date table just with a column for end of period dates (with no joins to other tables) so that the user himself can choose the last final month.
SumAmount 1000’s is a measure for actuals and Sumforecast is for the forecast.
@LarsTC , I guess you are from Denmark too. Would you mind PM’ing me with your information and I might reach out to you for some in-person advisory, if I cannot get it to work properly myself?
