loader image

Share:

Share on facebook
Share on twitter
Share on linkedin

How To Implement Rolling Forecast in Power BI

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

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

Write-Back And Edit

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

This can be done on the single period or for the entire forecast 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
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

I can’t help smiling when I see legacy BI/Planning application vendors -that were not so long ago laughing away Power BI- coming out with their own “Power BI Integration” after Microsoft has turned around the industry. These are more often than not nothing but “a marketing spiel” to build on Power BI’s awareness and of very limited use in real life application scenarios.

Across many mid-sized and large companies, finance and operations teams are at crossroads. This was one of the findings from the recent BPM Pulse 2021 survey which revealed that only a third of companies had truly harmonized strategic, financial, and operational plans.

Power BI offers unparalleled features for analysis and visualization: in this post we explain the quickest way to connect Oracle Netsuite in Power BI Deskop and how you can add comprehensive planning and budgeting.After your account has been setup, click on the “Integration” menu and go to the “Oracle Netsuite” section:

The default visuals in Power BI are not perfectly suited for financial reporting. There are ways to realize more advanced financial reporting requirements using DAX as Managility describes in Income Statement using DAX These, though typically require intermediate knowledge of DAX that many financial professional don’t necessarily have.

The Acterys Modeller is cloud based service (with on-premise installation option) that allows enables business users to automatically create optimal analysis models (star schema data warehouse) from a variety to ERP. accounting and SaaS systems. The created models are write and planning enabled and can be edited and extended as needed by business users e.g. for adding new products/attributes, account mappings, new scenarios etc.

Tags