You have a sales/forecast report in Power BI, and your manager asks you to add forecasts for future periods. You suddenly realize that these periods are not yet available in your data model, which sources its data from a source like SQL Server or Azure. How do you handle this?
Traditionally, you would reach out to the IT team or the SQL developers – but this may take time, ranging from a few hours to a few days. But you can do it in a much faster way using Inforiver. Let me illustrate this with an example.
ACME Corp has a sales report with performance data from Jan to Sep 2023.
The management wants to add a forecast for future periods Oct to Dec 2023. However, these values are missing in the Power BI query & data model.
Inforiver helps you add future periods seamlessly to your report. What’s more – once the month is available in the query/data model, Inforiver ensures that there is no conflict between the two. In addition, you can also seamlessly perform end-of-period closure where the forecast values are updated with actuals for the elapsed period.
Let us explore step by step how to add Oct to Dec 2023 periods to our report.
1. Click on Insert --> Forecast and the following prompt will pop up. Mention the periods for which you need to add the forecast. Inforiver allows you to generate a forecast for up to 5 years, starting from the current year. Click Next once you are done.
2. You will be shown a new popup window. Here we can mention how we want to populate the forecast for future periods.
Title: By default, the forecast measure is named ‘Forecast’.
Select Forecast Period: The starting period for which a forecast is being created. By default, this is set to the current month and year. It can be set to any month in the future. Your report will insert columns starting from this period.
Closed Period: Since we want to forecast from Oct 2023, Inforiver understands that the period from Jan to Sep 2023 has already elapsed/closed. You can designate the series from which you want to copy the data for these closed periods. In our case, it is the measure ‘Actuals’.
Period: In the last line, we mention that we want to insert the periods from Oct to Dec 2023, and we want to leave it blank. (Note that it is also possible to add another line and state that we want to create additional fields for Jan to Jun 2024, and auto-populate these fields from another measure, say, Budget 2024.)
By default, several methods are available to pre-populate forecasts for future periods, but we won’t go into the details here.
Copy Method: Method used to initialize the forecast. Since we chose to leave the fields blank, you will not see any methods by default. But if you temporarily switch the source series to Actuals, you will see these three methods.
a) Period Range: A range of months in the original series that will be used to populate the forecast. E.g., We can copy Jan to Mar data and copy it to Oct to Dec.
b) Single Period: The data for any period will be used to initialize the forecast. E.g., We can take Jan 2023 data and copy it to all months Oct to Dec.
c) Average of Period Range: The average value of a measure, over a user-defined range of time is calculated and used to initialize the forecast for Oct to Dec.
Click the “Create” button.
3. Once you click Create, you will notice a second series ‘Forecast’, which has an exact copy of the values for existing periods Jan to Sep. Three new blank forecast periods Oct to Dec are also added at the end.
4. Let us start entering our forecasts now. Double-click the value for Oct for the ALL region. A formula bar appears above the table. Type in ‘105m’ in the formula bar.
By default, the values get distributed equally across all the region and sub-regions.
In our case, we need to allocate the budget amount of 105M proportionally across geographies based on their performance in Sep.
Click on the context menu appearing on the Oct cell with the value 105.00 and choose ‘Distribute by weights of Actuals’ --> ‘2023 Sep’.
This will distribute Oct forecast data based on Sep actuals.
Similarly, we can add values and distribute the forecast for Nov and Dec 2023.
5. Once we move into October, your Power BI query and data model may start showing Actuals for Oct. As a result, you will start seeing both columns.
6. Once the October period elapses, you want to ensure that the Oct Forecast is updated with Oct Actuals, so that your full-year forecast is accurate. To do this, click on the Insert --> Manage Measures --> Measures
Click on the edit icon next to the forecast measure. You will be taken to the Data Input pane. Change the Forecasting Period value from Oct 2023 to Nov 2023. Click on “Update” as shown below.
This tells Inforiver that it must retain forecast values only from November. As a result, the October forecast will be automatically updated with actual data.
This ensures that your full-year forecast stays updated.
* * *
Using this simple technique, Inforiver ensures that you can add future forecast periods even if they are not available in your data model. It also ensures that you can update this forecast data with actuals as future periods elapse.
Explore more about Forecasting in Power BI here.
Inforiver is the fastest way to do everything in Power BI. It enables citizen developer productivity and unleashes true self-service with our intuitive and interactive no-code data app suite for Microsoft Power BI. The product is developed by Lumel Technologies Inc, who are #1 Power BI Visuals AppSource Partner serving over 3,000+ customers worldwide with their xViz, Inforiver, and ValQ offerings.