Forecasting With Power BI Data In Excel Using Cube Functions And The FORECAST.ETS Function, Part 2

In the first post in this series I showed a simple example of how you can use the FORECAST.ETS function in Excel in combination with the Excel cube functions to do forecasting with Power BI data. In this post I’ll show you how you can:

1. Make the range of data that you display from Power BI, and pass into the FORECAST.ETS function, dynamic and controllable from a slicer
2. Make the number of periods that you forecast for dynamic too
3. Put both the actuals and forecast data together in a single range and display that in a chart

The first problem, making the range of data returned from Power BI via cube functions dynamic, is reasonably straightforward because it’s a variation on a technique I blogged about last year here. A slicer can be used to select the date range, which in turn can be captured using the CUBESET function, and finally the MAKEARRAY function can be used to return a dynamic array of dates and associated measure values. Here it is working:

Cell B2 contains the CUBESET formula that is used to capture the set of selected items in the slicer (which is called Slicer_Date):

=CUBESET("Price Paid", Slicer_Date, "Date Range Set")

B5 contains the dynamic array formula that returns the dates selected in the slicer using the CUBERANKEDMEMBER function:

=MAKEARRAY(
CUBESETCOUNT($B$2), 
1, 
LAMBDA(r,c,CUBERANKEDMEMBER("Price Paid",$B$2,r))
)

C5 contains the dynamic array formula that returns the values for the Count of Sales measure for the date range in B5:

=MAKEARRAY(
CUBESETCOUNT($B$2),
1,
LAMBDA(r,c,
LET(
CubeValueResult,
CUBEVALUE("Price Paid",$C$4,INDEX($B$5#,r)),
IF(ISNUMBER(CubeValueResult), CubeValueResult,0))
))

The second problem is how to create a similar dynamic range of forecast dates and values. Here’s the solution working:

J3 contains the number of days to forecast. F5 contains a formula that returns a list of dates whose length is controlled by the value in J3, and which starts the day after the last day in the range returned by the formula in B5. Here’s the formula in F5:

=SEQUENCE($J$2)+MAX(DATEVALUE($B$5#))

The formula in G5 returns the forecast values for the date range returned by F5, based on the values returned by the formulas in B5 and C5:

=INT(FORECAST.ETS($F$5#, VALUE($C$5#), DATEVALUE($B$5#),7))

The third and final problem is how to combine these two ranges into a single range, like so:

The key to appending the Forecast values underneath the Count Of Sales values is the new VSTACK Excel function. So, for example, in I5 the following formula returns a dynamic array combining the dates used by the two ranges created above:

=VSTACK($B$5#, $F$5#)

For the Count Of Sales and Forecast columns I have padded the data out with zeroes, so for example the Count Of Sales column shows zeroes for the dates that contain forecast values and the Forecast column contains zeroes for the dates that contain Count Of Sales data. I did this by using VSTACK and appending/pre-pending an array containing zeroes created using MAKEARRAY. Here’s the formula for J5, ie the data in the Count Of Sales column:

=VSTACK($C$5#, MAKEARRAY($J$2, 1,LAMBDA(r,c,0)))

Here’s the formula for K5, ie the data in the Forecast column:

=VSTACK(MAKEARRAY(CUBESETCOUNT($B$2), 1,LAMBDA(r,c,0)), $G$5#)

I could have used the HSTACK function to combine these three dynamic arrays into a single array but there’s no real benefit to doing this, and not doing it makes it easy to use the technique Jon Peltier describes here to display dynamic arrays in a chart. I won’t repeat what he says but you need to create Names for these last three dynamic arrays in order to be able to use them in a chart.

One last thing: I haven’t said anything about how to make sure the forecast values are useful and accurate. That’s because I’m not a data scientist and I don’t have any good advice to share. This is a very important topic, though, and I’m very grateful to Sandeep Pawar for providing some tips on Twitter here.