Stretching and Compressing Time Series with Power Query and Power BI

The good folks from SumProduct have published a challenge for Excel that I find worth doing in Power Query instead:

The Challenge

Stretching or compressing a forecast so that the proportion of the original series will be maintained:

The Function

This is not so easy mathematically (as the solution-blogpost explains in detail), so the code for the function isn’t short as well:

How it works

The first 2 arguments are the new start- & end-dates and have to be entered as whole numbers of their relative position. The 3rd argument takes the table of the original forecast with one row containing all the values. The column names can have any value, as they will be ignored by the function.

You can download the file here: Allocation_StretchAndCompress

If you adjust the input values, the results of the native Excel-version will update immediately, while the results from Power Query have to be refreshed. So before doing so, you will see a lot of warnings in the reconciliation line:

But once refreshed (i.e.: Data -> Queries & Connections -> Refresh All or checking the table or query and then right-click your mouse -> Refresh), everything should be green (unless minor rounding differences occur).

Once you’ve loaded the table into your worksheet for the first time, your column widths might be all over the place (because of the tables default-setting is adjusting column width automatically (1)). Therefore change the settings in the properties like so:

The second adjustment is also very important, as this prevents existing content in your sheet to be shifted to the right, once this table should get larger. So make sure to adjust this setting as well.

The file also contains a query (“StretchingAndCompressing_FollowAlong”) where you can follow along every step of the function to see what’s happening. If you want to learn more about allocation techniques, please join our next User Group Meeting for Power BI for Accountants.

Reflection

Some advantages I see:

  • takes less footprint in the Excel-sheets
  • less error-prone, as there are no formulas in the worksheet that have to be protected
  • very dynamic, as more or less columns will be produced automatically
  • very quick to implement: Just copy the function, paste to new worksheet and search a place for the results. No need to enter and expand formulas and connect to existing values in the spreadsheet itself.
  • works in Power BI as well 😉

What did I miss? Please let me know in the comments!!!

Edit 1-May-2018: Fixed formula to cater for non-continuous-series as well. Leading and trailing nulls will be eliminated/ignored, but all nulls between the first and the last value will be considered as 0:

Enjoy & stay queryious 😉

Disclaimer: As so often, Bill Szysz came up with a more elegant version that you shouldn’t miss 🙂 :

 

Comments (2) Write a comment

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz