Fine-tune your Power BI date table to Business Central

You should maximize the use of date tables in Microsoft Power BI if you are working with Microsoft Dynamics 365 Business Central data as date tables provide you with greater flexibility and control over date calculations and filtering.

You want a date table that represents the full range of dates as shown in the data you are analyzing. The date range should automatically expand or contract based upon the latest data loaded.

Let's take a look at how to do this with an example. While you can define your date table from any transactional data in Business Central, we will in this example use the Sales Order table and the Order Date field. Let's create a date table defined by the minimum and maximum dates as found in the Order Date field in the Sales Order table.

1. The first step is to turn off the Auto Date/Time option in Power BI as you will now be using a date table. Click on File in the upper ribbon and click on Options and Settings. Then click on Options. Click off the Auto Date/Time field found under the Data Load. Click OK to save the settings.

Power BI Current File / Data Load Options

2. Let's connect with Business Central. Click on Get Data from the lower ribbon. Type in Business Central and click on the first Business Central line on the screen and then press Connect.

Power BI Get Business Central Data

3. Select the company you want to connect to and then scroll down and click on salesOrder under the Standard APIs V2.0 folder and press Load.

Power BI Table Selection for Business Central

4. Let's change the name of the salesOrders table that we just created so we are consistent with Power BI table naming conventions. Right mouse on the salesOrders table in the right Data pane and rename the table to SalesOrders.

5. Click on Modeling in the top ribbon and click on New Table in the lower ribbon.

Power BI Creating a New Table

6. Enter the DAX expression below as show below. This statement creates the date table based upon the first and last dates in the Order Date of the SalesOrder table.

Power BI DAX Statement

Date = CALENDAR(FIRSTDATE(SalesOrders[orderDate]), LASTDATE(SalesOrders[orderDate]))

7. In the Data pane rename the Date table that you just created to SalesOrderDates.

Power BI Table Rename for Business Central

8. Click on Data view on the left menu. Expand SalesOrderDates in the Data pane and click on Date. Click on Column tools in the top ribbon. In the lower ribbon change the Data type to Date and set the Format to the Short Date.

Power BI Formatting Date Type and Short Format

9. Click on Table tools in the top ribbon and then click on Manage relationships in the lower ribbon. Click the New... button on the form. Enter SalesOrders and SalesOrderDates as the two tables. Make sure you click on orderDate in the SalesOrder table and Date in the SalesOrderDates table. Set the Cardinality to many to one (*:1). Click on the OK button and then the close button to save your changes.

Create Relationship in Power BI Between Two Tables

You are now done. Each time you load salesOrders the SalesOrderDates date table will automatically adjust to the date range represented by the salesOrders table.