Power BI & Business Central: Mastering Sales Forecasting

In today's fast-paced business world, predictive analytics is the cornerstone for making informed decisions. With ever-growing datasets and increasing demands for insightful visualizations, tools like Power BI have become quintessential. Now, imagine combining the power of Power BI with Microsoft's Business Central to forecast sales trends - sounds enticing, right?

Master Business Central Sales Forecasting Using Power Bi and Regression Analysis

Welcome to this comprehensive guide where we venture into the realm of regression analysis using Power BI's LINESTX function with Business Central's sales data. If you've ever wondered about how to gauge the trajectory of your sales using historical data, then you're in for a treat.

Why Regression Analysis? Regression analysis isn't just a fancy term thrown around by data scientists. It's a robust method for forecasting and understanding relationships between variables. For businesses, this means predicting future sales with a high degree of accuracy based on past data.

The Power of LINESTX in Power BI: With Power BI's LINESTX function, we can tap into the world of regression analysis. It allows us to derive the intercept and slope from our historical sales data, which can then be extrapolated to forecast future sales trends.

Endless Possibilities with Business Central Data: Business Central's itemLedgerEntries table is a goldmine for data enthusiasts. By leveraging the LINESTX function, the possibilities are endless. Whether you're segmenting by customer demographics, product categories, vendors, or any other variable - the sky is the limit in predicting the future.

In this guide, we will take a deep dive, step by step, into setting up our data tables, molding them as per our needs, and then weaving the magic of regression analysis to produce actionable insights.

So, whether you're a seasoned Power BI user or just starting out, strap in and get ready for a ride into the future of sales forecasting!

Creating and Collecting Data for Our Regression Analysis

To perform our regression analysis on Business Central data we need to load two existing Business Central tables and create two new tables. Let's do it!

Creating the Query1 Table

1. Click on Get data in the top menu and enter blank query in the search box. Connect to the Blank Query.

Create a Blank Query in Power Bi

2. Enter the following in the box at the top. We will use a range of -21 to 28 to generate dates for the trend analysis based upon the Business Central demo data on hand. The first figure tells the program that we will look at 21 months of historical data. We will then roll forward 28 months so we can forecast 6 months in to the future. What figures you use is a business decision. Adjust these values for your business needs.

= List.Numbers(-21,28)

Another example is when you want to use six months of historical data to forecast 6 months into the future. You would enter (-6,13) at the prompt.

Create a List in Power Bi

3. We will now transform this List into a Table. Click on Transform in the top menu and then click on the To Table in the concert box. This new table will be our sequence table that we will combine with the Items Sales table. Save and close the table.

Transform a List to a Table

Loading the Item Ledger Entries Table

4. Next we want to add the Business Central itemLedgerEntries table. You can find an example on how to do this in my Fine-tune your Power BI date table to Business Central blog. Once you have loaded the table, filter the entryType column to Sale type. Remove all columns except the itemNumber, postingData and salesAmountActual columns. Save and close the table.

Load and Filter the itemLedgerEntries Table

Loading the Items Table

5. Add the Business Central Items table and remove all the columns except for the number and displayname columns. Save and close the table.

Load the Business Central items Table

Creating the Item Sales Table

6. Once again, add the Business Central items table. But, instead of clicking on the Load button click on the Transform button. Remove all the columns except for the number and displayname columns. Click on the Add Column and then Custom Column in the top menu.

Create a Custom Column in the Items Table

7. Enter Query1, the table you created in step one in the Custom column formula and click OK.

Reference the Query1 Table

8. Click on and expand the Custom column and take the default on the next screen.

Expand the Custom Table

9. Rename the Custom column to MonthNumber. You should now see the following columns. Save and close the table.

Rename the Column to MonthNumber

10. Rename your newly created items (2) table to Item Sales.

Reviewing and Creating Table Relationships

11. Go to the Model view and make sure that there is a one to many relationship between the items table and the itemLedgerEntries and Items Sales tables using the item number/number columns.

Review the Table Relationship in Regard to the Item Number

Modifying and Expanding the Item Sales Table

12. Go to the Table view and click on the Item Sales table in the Data pane on the right. Click on New Column in the top menu and enter the following. Save the Start Date column.

Start Date = 
    var _x = DATE(year(TODAY()),MONTH(TODAY()),1)
return 
    edate(_x,'Item Sales'[MonthNumber])

13. Click on New Column again and enter the following. Save the End Date column.

End Date = 
    var _x = DATE(year(TODAY()),MONTH(TODAY()),1)
return 
    EOMONTH(_x,'Item Sales'[MonthNumber])

14. Click on New Column again and enter the following. Save the Sales column.

Sales = 
    calculate(sum(itemLedgerEntries[salesAmountActual]),
        DATESBETWEEN(itemLedgerEntries[postingDate],'Item Sales'[Start Date],'Item Sales'[End Date]),
        filter(itemLedgerEntries,itemLedgerEntries[itemNumber]='Item Sales'[number]))

15. Add a new column one more time. Click on New Column and enter the following. Set the _history variable to the number of months containing sales data that you will use in your regression analysis. The _history variable should parallel the value you set in step number 2. This DAX code includes the LINESTX function derives the slope and intercept values. Save the Trend column.

Trend = 
    Var _history = 14
    Var _item = 'Item Sales'[number]
    Var _line =
        LINESTX(
            topn(_history,Filter('Item Sales','Item Sales'[number]=_item),'Item Sales'[MonthNumber],ASC ),
            [Sales],
            [MonthNumber]
            )
    Var _intercept =
        SELECTCOLUMNS ( _line, [Intercept] )
    Var _slope =
        SELECTCOLUMNS ( _line, [Slope1] )
    Var _month =
        'Item Sales'[MonthNumber]
    Var _y = (_month * _slope) + _intercept
RETURN
    _y

16. Your Item Sales table columns should now look like this:

The Item Sales Table Columns

Creating Visuals to Show Our Results

Now that you have the ability to forecast future sales by month you can create many different visuals. Let's go ahead and create two simple visuals.

17. Click on Report view in the left pane. Select a Table visual from the top pane. Add the Start Date, Sales, and Trend columns from the Item Sales table in the Data pane. Rename the columns to Month, Sales, and Trend.

Add the Table Visual to the Power Bi Report

18. Click on the report canvas and then select a Line Chart visual from the top pane. Add the Start Date to the X-axis. Add the Sales and Trend columns from the Item Sales table to the Y-axis. Rename the columns as before.

Add the Line Chart to the Power Bi Report

19. Let's add a slicer to the canvas. Click on the report canvas and then select a Slicer visual from the top pane. Add Item Number from the Items table to the slicer. You can now click on an item number and the report will calculate a forecast and show the appropriate data on the Line Chart and Table visuals. We are done.

Add an Item Number Slicer to the Power Bi Report

Conclusion

And there you have it! Harnessing the immense potential of regression analysis through Power BI's LINESTX function in tandem with Business Central's sales data, we have embarked on a journey from raw data to insightful visuals. This endeavor not only showcases the profound capabilities of Power BI and Business Central but also underscores the importance of making data-driven decisions in today's business landscape.

But remember, while the tools and techniques we've covered are powerful, they are just the beginning. True value comes from the application – integrating these methods into your daily operations, continuously refining your models, and never settling for the status quo. As businesses evolve, so should our analytical approaches. Use what you've learned here as a foundation, and let your creativity soar in discovering new ways to glean insights from your data.

Lastly, I hope this guide has demystified the world of regression analysis for you and has provided you with a roadmap to enhance your sales forecasting endeavors. Always strive for a deeper understanding, and never hesitate to experiment with new models or data sources. The future might be uncertain, but with tools like these at our disposal, we can certainly make better-educated guesses.

Thank you for joining me on this enlightening journey. I encourage you to dive deep, explore further, and keep pushing the boundaries of what's possible with Power BI and Business Central. Until next time, happy forecasting!