Share on facebook
Share on twitter
Share on linkedin

How To Implement Rolling Forecast in Power BI

Let’s learn how to implement rolling forecast with Power BI in detail using Acterys Power BI tool.

In this blog we cover how you can realize a rolling forecast with Power BI (this approach works equally with Excel Pivot Tables).

What is Rolling Forecast in Power BI?

A rolling forecasts is a widely used concept for financial analysts that shows actual data until a cut off date and after that future assumptions like budget or forecast values.

It combines Year to Date (YTD) actual data and budget/forecasts. Some important differences between the Annual Budget and Rolling Forecast are listed below.

It looks something like this:

Rolling forecast actual-forecast in Power BI
Rolling forecast actual-forecast in Power BI

Simple DAX

This can be achieved with a simple DAX calculation where you refer to the cut off date “forecast parameter” and just return “actuals ” before and your forecast data after. Similar to:

Rolling Forecast = If(max(DimDate[Date])<max(‘Forecast Parameters'[Closing Date]),[AC],[FC])

In a properly structured data model – Acterys generates this automatically– with an account, a scenario dimension, and a single value column nothing more is required. Just put your accounts hierarchy in the rows and your date dimension in the columns of a matrix visual e.g. Power BI Matrix or Acterys Matrix.

The forecast parameter can be managed using a Dax calculation, edit table in Power BI or with Acterys Table Edit which allows users without any admin rights (Edit Table and changing DAX for maintaining the cut off date only works in Power BI Desktop) to edit data in relational tables directly in Power BI service or Desktop:

Setting rolling forecast parameter
Setting rolling forecast parameter

Writeback and Edit in Power BI

The Acterys Matrix Power BI Custom Visual also enables users to enter and edit the forecast values directly in the matrix:

Rolling forecast data entry
Rolling forecast data entry

Users can forecast data values either for a single period or for the entire forecasting period. Particularly easy with Acterys Matrix as it supports expand and collapse in columns which the native Power BI Matrix does not:

rolling forecast aggregate
Financial Budget rolling forecast aggregate

The forecasting write-back and user rights management logic (automatically setting write back rights to only the forecast period and locking of actuals) is now automatically available for Acterys users.

To try it out, start your free trial here: or speak to our chat team for any further questions.

Recent Posts

Next Gen Unified Analytics & Planning: Acterys 21.2 The new Acterys version 21.2 marks an unparalleled set of innovations around

Whether you are  budding a startup that has secured Series B funding or a global enterprise, it is likely that

Forward thinking organizations across the globe are now enabling their finance teams to link operations with strategies. Moving beyond their

Top 5 Tips & Tricks For Planning, Budgeting and Forecasting in Power BI & Excel In this blog I am

Planning and Analytics [Infographic]

It is not uncommon for companies to have separate planning and analytics processes or legacy solutions that result in more