Table of Contents
Let’s learn how to implement rolling forecast with Power BI in detail using Acterys Power BI tool.
This blog will show you how you can carry out a rolling forecast with Power BI. Not: this also works with Excel Pivot Tables.
What is Rolling Forecast in Power BI?
Financial analysts use rolling forecast to show actual data until a cut-off date, then predict 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:
Simple DAX
A simple DAX calculation refers to the cut-off date “forecast parameter” and return “actuals ” before, as well as your forecast data after. Let’s say:
Rolling Forecast = If(max(DimDate[Date])<max(‘Forecast Parameters'[Closing Date]),[AC],[FC])
Acterys automatically generates this with an account, a single value column and a scenario dimension, and nothing else. All you have to do is add 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 Power BI forecasting formula can be managed using a DAX calculation, edit table in Power BI or with Acterys Table Edit, through which users can edit without any admin rights (Changing DAX and edit table maintains the cut off date and works only in Power BI Desktop) to edit data in relational tables directly in Power BI service or Desktop:
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:
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:
The rolling 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. The solution can work with scenarios like forward forecasting using DAX as well.
To try it out, start your free trial here or speak to our chat team for any further questions.
