Combine actuals and forecast

Hi!

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.

Brilliant people of Enterprise, please chip in :slight_smile:

Warm regards,
Jannik

2 Likes

Hi there,

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.

Full module here

Important ones to go over first.

Also here is a detailed webinar session on budgeting where I build things up from scratch.

I cover the model part here quite a bit

Also another one to review here, I highly recommend also.

There’s a bit to learn around this to understand how it all works. These resources though will get you there, I’m very confident in that.

See how you go from here.

Thanks
Sam

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.

I hope this helps you.

Best,
Lars

Thank you, guys! I will look into both :slight_smile:

@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?

@JannikZ

Not sure how to PM here, but you are welcome to contact me on:
larsSNABELAfinpower.dk