3 Things to Know About Dynamics 365 Business Central

Just got done with two days of learning about Dynamics 365 Business Central at the Microsoft Center in New York City. Before the training, my main focus was on how to use it with Power BI. This was my first training related to Business Central and the trainers, Craig, @craigcrescas, and Tom did a great job!!

Dynamics 365 Business Central is an all-in-one business management solution that’s easy to use and adapt, helping you connect your business and make smarter decisions. Built on functionality within Microsoft Dynamics NAV and adaptable to extend business applications to other Microsoft Cloud Services such as Office 365, Microsoft Flow, Power BI and PowerApps.

In this post I’m going to discuss some of my biggest take a ways – Ready To Go, Built to Develop and Office 365 and Power BI integration.

Ready To Go

If you haven’t found it yet, go to http://aka.ms/readytogo. There you we find information on:

* “Ready To Go” online learning

* “Ready To Go” coaching

* “Ready To Go” platform

“Ready To Go” online learning

The “Ready to Go” online learning catalog is an extensive library of training materials, hosted on the Dynamics Learning Portal. You can find the “Ready to Go” online learning catalog here.

This catalog contains readiness information for marketers, business decision makers, architects, and developers. The content addresses the needs of both reselling partners of Dynamics 365 Business Central as App builders who want to provide Apps on Microsoft AppSource. Currently there is over 15 hours of online learning. Here are some of the catalog categories:

Online learning catalog

“Ready To Go” coaching

The “Ready to Go” coaching is executed by Microsoft Development Centers and Master VARs, which are independently owned and operated. They bring technical services and coaching to market and have industry specific knowledge that you can contract and leverage when developing your offerings. Over time they have developed lots of best practices by supporting multiple partners, they also have strong ties with Microsoft’s R&D teams and are always up to date on the latest strategy and product innovation.

The “Ready to Go” validation workshop is offered by all Development Centers and Master VARs at fixed price and is designed to coach you in bringing your Dynamics 365 Business Central offerings to market faster and with less friction by providing individual coaching. This 8-hour workshop helps partners make the right decisions and is based upon a unified checklist and set of training materials. The workshop validates and coaches the partner’s readiness on their go-to market business model, marketing plans, architectural plans, app Development and internal readiness.

Where needed the Microsoft Development Centers and Master VARs have more offerings to support you. We recommend reaching out if you want more information.

“Ready To Go” platform

To support you in App development, testing and learning scenario’s, the “Ready to Go” platform is a place where you can find current, upcoming and daily builds of Dynamics 365 Business Central. It also holds a way to provide feedback to the Microsoft engineers working on Dynamics 365 Business Central. These builds are made available to you on the Microsoft Collaborate platform. To get access to the “Ready to Go” platform, you need to go through these steps:

  1. Register on Microsoft Collaborate using your AAD Global Admin account.
  2. Contact us, once registered on Microsoft Collaborate. Your company’s Azure Active Directory (AAD) global administrator should contact us to complete the on-boarding. We need to manually assign you to the right programs and engagements. Expect a response from us within 1-2 business days.
  3. The Ready to Go team will notify you and confirm your registration.

Built for Development

Dynamics 365 is a flexible business platform that can be scaled to suit and meet your business needs in terms of size and complexity. There are three ways that the system can be customized by either using – Personalize, Designer or Visual Studio Code for al language development. Personalize can be done by the Business Central end user by simply navigating to the gear icon in the top right corner of the window/screen you are on and selecting “Personalize”.

Personalize Screenshot

For me, I’m on the Sales Order window. I now select the Sales Order line detail section and select the “Field” option in the top gray bar. From there you will get the “Add Field to Page” section on the right of the below screenshot. Use the search function and type in “Bin”. Now drag the Bin Code field over the Lines section of the Sales Order. Select “Done” to complete your personalization.

Screen Personalization

Designer is a little more work in the customization process but is applied globally instead of at a personal level. First you will need to create a Sandbox. A sandbox environment (Preview) is a non-production instance of Business Central. Isolated from production, a sandbox environment is the place to safely explore, learn, demo, develop, and test the service without the risk of affecting the data and settings of your production environment. Here is the link to how to create one. Once you’re done, you should see “Sandbox” in the black tool bar on the top right of the screen. Selecting the gear icon once again will bring up the additional “Designer” option. Both the Designer and custom al language option should be handled by a developer or more technical user. You can read more about these option in the “Ready To Go” online learning Technical catalog categories discuss above.

Power BI Integration

Getting insights into your Business Central data is easy with Power BI and the Business Central content packs. Power BI retrieves your data and then builds an out-of-the-box dashboard and reports based on that data.

You must have a valid account with Dynamics 365 and with Power BI. Also, you must download Power BI Desktop if you wish to create your own Power BI reports. Power BI content packs require permissions to the tables where data is retrieved from.

How to Connect

  1. Select Get Data at the bottom of the left navigation pane. You may also get starting from within Dynamics 365 Business Edition. From the role center, navigate to Report Selection in the Power BI Role Center part. Select either Service or My Organization from the ribbon. When either of these actions are selected, you will be taken to either the Organization gallery in Power BI or to the services library in Power BI, which will also be filtered to only display content packs related to Dynamics 365 Business Central.Get Data
  2. You may also get starting from within Dynamics 365 Business Edition. From the role center, navigate to Report Selection in the Power BI Role Center part. Select either Service or My Organization from the ribbon. When either of these actions are selected, you will be taken to either the Organization gallery in Power BI or to the services library in Power BI, which will also be filtered to only display content packs related to Dynamics 365 Business Central.
  3. In the Services box, select Get. This will open a window with the AppSource and Apps for Power BI apps.Get Services
  4. Select Apps from the Apps for Power BI apps tab, search for “Business Central” content pack that you want to use, and then select Get it now for the App you want to load. I choose “Microsoft Dynamics 365 Business Central – Sales. Dynamics 365 Business Central Apps
  5. When prompted, enter the name of your company in Dynamics 365 Business Central. This is not the display name. The company name can be found on the ‘Companies’ page within your Dynamics 365 Business Central instance. Company Name
  6. You can find the Company Name in Business Central by searching with the magnifying glass and typing in “companies”. Your screen should look like the below screenshot. Business Central Companies screen
  7. Once connected, a dashboard, report and dataset will automatically be loaded into your Power BI workspace. Power BI Business Central - Sales Report
  8. When completed, navigate back to Business Central’s home page and down to the bottom right corner. Click on the Ellipsis, “…”, in the top right of the Power BI section to Select Report and enable the reports you just deployed. Select ReportEnable Report
  9. You should now see your report and you can use the Previous Report and Next Report menu options to view all reports available.

Stay tuned for more on Business Central and Power BI

Advertisements

Power BI Dynamics GP Sales and Inventory Analysis Report Modifications

Recently, I’ve made some updates to my Power BI Sales and Inventory Analysis Reports and wanted to share those with you. On the Sales Analysis report, I’ve added the ability to segment my sales revenue by growth rate and compare that growth rate to the previous period selected. Here are the steps I used to add the changes to the report.

  1. First, I clicked on the “Enter Data” icon from the Home tab in Power BI Desktop and then entered the following information.Power BI Create Table
  2. I then created a DAX Sales Growth measure with the following code.
    Sales Growth = DIVIDE([Sales CY],[Sales LY],0)-1
  3. Next I created a SAX measure to segment the data based on the Sales Growth Groups that we created in step 1.
    Sales per Growth Group =
            CALCULATE (
                [Sales CY],
                FILTER (
                    VALUES ( Customers[Customer Class] ),
                    COUNTROWS (
                        FILTER (
                            'Sales Growth Groups',
                            [Sales Growth] >= 'Sales Growth Groups'[Min]
                                && [Sales Growth] < 'Sales Growth Groups'[Max]                     )                 )                     > 0
                )
            )
    
  4. Next, I modified my Product by Sales and Customer Class by Sales visuals to use the new Sales per Growth Group measure by dragging the segment from the Sales Growth Group table into the Legend field, Sales Growth measure into the Tooltips and Sales per Growth Group measure into the Values field.Screenshot of field wells
  5. Below is a screenshot of the end results. You can now clearly tell which segments are performing better or worse than last year.Power BI Sales Summary Report

My next modification was to my Inventory Order Analysis report. This one was just a small change by adding one DAX measure to calculate slow moving inventory and a Table visual to view the results.

  1. First, I added a DAX measure for slow moving inventory
    SM Inventory Value = 
    VAR SumOf = 
    CALCULATE(
        SUMX(
            Inventory,
            Inventory[Quantity on Hand] * Inventory[Unit Cost]
        )
    )
    VAR Range = DATESINPERIOD(Dates[Date], EDATE(TODAY(), -12), -24, MONTH)
    RETURN
    CALCULATE(
        IF(
            COUNTROWS(
            INTERSECT(
               VALUES(Inventory[Last Sale Date]),
               Range)
           ) > 0, 
           SumOf, 0
        )
    )

  1. Then I added the following columns and measure to a table visual to show my slow moving inventory.Table Visual Field Wells Screenshot
  2. Below is a screenshot of the modification to the Inventory Order Analysis report.Power BI Inventory Order Analysis

Until next time, Enjoy the code and Happy Analyzing!

How to Create a Better RMA Dashboard For Mammoth Saving

OK, maybe not mammoth saving but controlling quality and costs during the Return Material Authorizations (RMA) process has a multiplicative effect for growth in a company. Finding new ways to improve perfect order performance continually reduces RMA and increases customer satisfaction leading to more sales.

RMAs are a direct measure of product quality and a products’ nonconformance to customers’ specifications and requirements. They are issued for a wide variety of reasons. The RMA module within Dynamics GP is designed to provide detailed traceability for both customer and supplier returns. Before we get to designing our Power BI RMA dashboard let’s look at Dynamics GP RMA module.

Return Material Authorization Module

The Returns Management module for Microsoft Dynamics GP enables you to meet customer and vendor demands for product and part returns by streamlining tasks and improving your responsiveness to customer queries and complaints. Generate returns from historical customer invoices, service calls, or manually. Return an item to a vendor to fix it within your company and automatically transfer items from the returns warehouse to the main warehouse. Give your customers up-to-date information about the status of their returned items.

You can use Returns Management to enter, set up, and maintain your RMA and Return To Vendor (RTV) documents. If Returns Management is integrated with Service Call Management, an RMA is created automatically from a returnable parts line on a service call. If Returns Management is integrated with Sales Order Processing, you can select items directly from the historical Sales Order Processing invoice that was generated for a specific customer.

Dynamics GP’s RMA Life Cycle

When operating a returns warehouse, you sometimes interact with customers who need to return equipment for various reasons. When they bring you a part, you complete one of a few tasks: issue a credit, repair and return the item to the customer, or replace the item. You also may be accepting the item in exchange for an item you already provided to the customer. Once you’ve completed your transaction with the customer, you have a few more options: repair the item and return it to your inventory, return the item to the vendor, or scrap the item.

The following diagram outlines the life cycle of RMAs, from entry through completion. When Returns Management is integrated with the other modules of the Field Service Series (Service Call Management, Preventive Maintenance, Contract Administration, and Depot Management), many new options and paths become available.

RMA Life Cycle

You can create RMAs from two different points of access throughout Field Service:

  • Manual entry in the RMA Entry/Update window
  • From return lines for returnable items on a service call

Entry in the RMA Entry/Update window is the method described in this manual. Refer to the Service Call Management documentation for more information regarding service calls and returnable items.

RMA types Inside Dynamics GP

RMA documents are used to track an item return from your customers. The available RMA document types are as follows:

  • Credit – Provide a credit to your customer’s account in Receivables Management for the value of the items the customer returned to you.
  • Replacement – Provide the same item, or a similar item, as a replacement to your customer. You must receive the original item from your customer before you send the replacement item on a new order document in Sales Order Processing.
  • Advance Cross–ship – Provide the same item, or a similar item, as a replacement to your customer. You can send the replacement item using a new Sales Order Processing order document prior to receiving the original item from your customer.
  • Repair and Return – You, or your vendor, will repair the item that is received from the customer. Your customer will receive the item after it’s been repaired.
  • None – The customer’s original item is picked up by your field service technician and returned to your returns warehouse. This type of RMA document was designed to integrate directly with Service Call Management.

Analyzing Our RMA Data with Power BI

With all the RMA data we will be using the following three visuals and associated measures:

  • Rate of Return – This is an incredibly useful KPI in a distribution center, especially when segmented by cause for return. Identifying causes for returns — damage, late delivery, inaccurate product description, wrong item shipped, etc — helps warehouse managers address underlying issues, and make necessary improvements.Number of Units Returned/Number of Units Sold = Rate of Return.
  • Perfect Order Rate – This KPI measures how many orders your warehouse successfully delivers without incident: the correct item, shipped on time and received in good condition by the customer who ordered it. Lean practices help identify errors or inaccuracies before orders leave the warehouse.Orders Completed Without Incident/Total Orders Placed = Perfect Order Rate
  • RMA Pareto Analysis – Done on the top 20% of factors that drive 80% of the returns. This will make cause troubleshooting more efficient, leading to permanent solutions to problems that may be causing RMAs to begin with. I’m not going to go into detail on how to build this chart in this blog post. You can find the steps on how to complete it here: https://powerbi.tips/2016/10/pareto-charting/.

First, we need to get our Dynamics GP RMA data into Power BI and we’ll do that with the SQL script below:

WITH CTE
AS (SELECT
  300 AS Company_Key,
  b.RETDOCID AS [RMA Document ID],
  b.LNSEQNBR,
  a.Return_Record_Type,
  b.RETREF AS [RMA Reference],
  b.RETSTAT AS [RMA Status],
  b.RETTYPE AS [RMA Type],
  a.COMPDTE AS [DocDate],
  a.USERID,
  b.OFFID AS [Office ID],
  b.LOCNCODE AS [Location Code],
  a.CUSTNMBR,
  b.SOPTYPE,
  b.SOPNUMBE AS [Invoice Number],
  b.SVC_RMA_Reason_Code AS [RMA Reason Code],
  b.SVC_RMA_Reason_Code_Desc AS [RMA Reason Code Description],
  b.ITEMNMBR,
  b.QUANTITY,
  b.UNITCOST AS [Unit Cost],
  b.EXTDCOST AS [Extended Cost],
  b.[Credit_SOP_Number]
FROM dbo.SVC05200 b (NOLOCK)
JOIN dbo.SVC05000 a (NOLOCK)
  ON b.RETDOCID = a.RETDOCID
WHERE b.QUANTITY > 0
AND b.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) -- Didn't want everything so limiting to last 4 years of RMA data 
AND a.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) -- Didn't want everything so limiting to last 4 years of RMA data

UNION ALL

SELECT
  300 AS Company_Key,
  b.RETDOCID AS [RMA Document ID],
  b.LNSEQNBR,
  a.Return_Record_Type,
  b.RETREF AS [RMA Reference],
  b.RETSTAT AS [RMA Status],
  b.RETTYPE AS [RMA Type],
  a.COMPDTE AS [DocDate],
  a.USERID,
  b.OFFID AS [Office ID],
  b.LOCNCODE AS [Location Code],
  a.CUSTNMBR,
  b.SOPTYPE,
  b.SOPNUMBE AS [Invoice Number],
  b.SVC_RMA_Reason_Code AS [RMA Reason Code],
  b.SVC_RMA_Reason_Code_Desc AS [RMA Reason Code Description],
  b.ITEMNMBR,
  b.QUANTITY,
  b.UNITCOST AS [Unit Cost],
  b.EXTDCOST AS [Extended Cost],
  b.[Credit_SOP_Number]
FROM dbo.SVC35200 b (NOLOCK)
JOIN dbo.SVC35000 a (NOLOCK)
  ON b.RETDOCID = a.RETDOCID
WHERE b.QUANTITY > 0
AND b.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) -- Didn't want everything so limiting to last 4 years of RMA data
AND a.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0)) -- Didn't want everything so limiting to last 4 years of RMA data

SELECT
  b.*,
  rs.STSDESCR AS [RMA Status Description],
  iv.ITMCLSCD AS [Item Class]
FROM CTE b (NOLOCK)
LEFT JOIN dbo.IV00101 iv (NOLOCK)
  ON iv.ITEMNMBR = b.ITEMNMBR
LEFT JOIN dbo.SVC05500(nolock) rs
  ON b.[RMA Status] = rs.RETSTAT
WHERE b.QUANTITY > 0

With the data now loaded into Power BI Desktop, I created relationships with my Inventory, Date and Customer dimension that I already had in Power BI Desktop from my previous blog posts.

Creating Our Measures

Time to build the Rate of Return Measure. Rather than create one big DAX measure we will be building several small measures that build upon each other. Here are the DAX formulas that we will be using for the Rate of Return measure:

  • Return of Lbs CY
Returns Lbs. CY = CALCULATE(
    ABS(
        SUM(Returns[Total Return Lbs])
        )
)
  • Lbs. CY

Lbs. CY = SUM(Sales[ExtendedWeight])

  • Rate of Return
Rate of Return = DIVIDE 
    ([Returns Lbs. CY], [Lbs. CY], 
    Blank()
 )
  • Monthly Average Rate of Return
Monthly Avg. Rate of Return = AVERAGEX( 
    DATESINPERIOD( 
        Dates[Date] , 
        LASTDATE( Dates[Date] ),
        -30,
        DAY),
    [Rate of Return]
)

With our calculations now complete, we create a line chart with Date on the Axis and Monthly Avg. Rate of Return for the Value as seen in the bottom left line chart visual of our Returns Analysis report screen shot below.

Up next, our Perfect Order Rate measure. Once again we will build several small calculation and add them together to create our Monthly Avg. Perfect Order Rate. Here are the DAX formulas that I used for this:

  • Total Invoice Count – Since my data includes line level detail for each Sales Order type in the Sales Table I’ll count all lines invoice and evaluate each line later to make sure it was a perfect order.
Total Invoices Count = CALCULATE(
    COUNTROWS('Sales'),
    FILTER('Sales','Sales'[SOPTYPE] = 3)
    )
  • Perfect Order – Now we will determine if the order was fulfilled before the customer’s required date.
Perfect Order = CALCULATE(COUNTROWS
    ('Sales'),
    FILTER('Sales','Sales'[SOPTYPE] = 3),
    FILTER('Sales',[Document Date]<='Sales'[Req Ship Date])
    ) 
  • Perfect Order Rate
Perfect Order Rate = DIVIDE(
    [Perfect Order],[Total Invoices Count],
    BLANK()
    )
  • Monthly Average Perfect Order Rate
Monthly Avg. Perfect Order Rate = AVERAGEX( 
    DATESINPERIOD( 
        Dates[Date] , 
        LASTDATE( Dates[Date] ),
        -30,
        DAY),
    [Perfect Order Rate]
)

With all of our DAX measures complete, we can create the visuals needs to Monthly Average Perfect Order Rate and Monthly Average Return Rate of Lbs. Here is what my final Returns Analysis report looks like.

RMA Dashboard

Until next time, Happy Analyzing!

3 Secrets To An Awesome ERP System!

Part of being a Microsoft Dynamics Consultant is seeing all the inventive ways clients use the software we deploy and take that knowledge and provide best practices to everyone! Here are just some of the things to enhance your system processes:

  • Best Practices
  • Customization and Modifications
  • Reporting Enhancements

Best Practices

Customization, Modifications and Third Party Add-ins

  • General Ledger, Sales Distribution, and Payables Distribution intercompany Excel paste – James Lyn’s Excel Paste add-in. is great at extending Dynamics GP’s out of the box functionality. While you’re on his site, check out his other add-ins like GP batch attach for Payables.
  • Create Dynamics GP Marcos or use PowerShell scripts to automate tasks – i.e. Macro to log into Dynamics GP and run inventory reconcile process or reboot your web client servers to remove hung processes.
  • Custom workflows and reporting – reporting to provide detail information on current/open and historical/approved workflows. Find out how to do that here.

Reporting Enhancements

Dynamics GP comes with some good reporting capabilities:

  • Management Reporter.
  • Excel refreshable reports.
  • SmartList.
  • Jet Express for Dynamics GP.
  • Solver’s BI360.
  • Power BI.

With Dynamics GP 2018 you can now deploy Power BI GP content pack or embedded Power BI visual inside of Dynamics GP. So what do the Power BI content pack visuals look like and how do we get them installed? As of Microsoft Dynamics GP 2018, the GP OData service was updated to OData version 4. This redesign also brought on paging and filtering of OData requests. This will create a more stable and robust platform for delivering Microsoft Dynamics GP content to authenticated users. The Power BI content pack features sample reports for Financial, Sales, Purchasing and Inventory data. Each report utilizes relationships built between GP tables and various Filters that can be used to display the information that is important to you. You can also review the included Calculated Columns as examples for including calculations on your Power BI reports such as Net Debit/Credit, Profit, and Item Sales amounts.

In order to use the Power BI Content Pack with Dynamics GP, install OData Services. Once this is complete, you will have to publish the following Data Sources inside Dynamics GP. (Administration > System > OData > Data Sources) The following Data Sources will need to be published for the GP content pack.

  • Accounts
  • Account Transactions
  • Customers
  • Inventory Sales Summary Period History
  • Inventory Transactions
  • Item Quantities
  • Purchase Line Items
  • Purchase Requisition Lines
  • Purchase Requisitions
  • Receiving Line Items
  • Sales Line Items
  • Vendors

The Power BI Content Pack will also have to be configured to point to you’re existing Microsoft Dynamics GP OData Service. This can be done by modifying the existing Data source in Power BI, or by creating a new data source and configuring the content to use the new source. The screenshots below show what the GP content pack sample reports look like.

Finance Dashboard

Finance

Sales Dashboard

Sales

Purchasing Dashboard

Purchasing

Inventory Dashboard

Inventory

Enhancing Our Dashboards

These GP content pack reports are a good starting point and can speed up the process of implementing a Power BI solution. With a little work from your Microsoft Dynamics Consultant, we can provide you so much more. In February of 2017, I started a blog series that provided a “how to” on building a Finance, Sales, Customer, Product, and Inventory dashboard. Since my main goal was to show an update of the Excel multi-company dashboard, I choose to first build a small DataMart and integrate the data from Dynamics GP databases before building my visuals. Follow the links below to find out how I built each one of the enhanced dashboards.

Enhanced Finance Dashboard

The finance dashboard, from the blog series, now provides a summary profit & loss statement that you can drill down into line level detail. Additionally, you see total sales by inventory class and customer class and a weekly moving average.

Enhanced Finance Dashboard

Enhanced Sales Dashboard

The sales summary dashboard provides an analysis of sales by inventory item class, customer class and weekly moving averages for total sales dollars and transactions. The report also includes a cumulative sales and cost comparison.

Enhanced Sales

Product Performance and Inventory Reorder Dashboard

In the product performance dashboard, I’ve added to compare total profits and profit margin, cost by warehouse and ABC segmentation analysis. Additionally, there is a weekly moving average cost comparison chart at the bottom of the report.

Enhanced Product Performance

For my inventory reorder analysis report, I’m using some discussed by Belinda Allen in her, Inventory Item Reorder Dashboard. I converted it to Power BI to help your procurement manager evaluate what’s on hand, allocated to open orders and items sold within a given time period.

Enhanced Inventory Dashboard

Future Developments

Returns are generally thought of a loses and return percentages can be dependent both the type of product and the company’s returns policy. While the average industry rate is four percent, consumer durable goods can range from two to 10 percent and apparel can be in excess of 20 percent. There are several reasons for merchandise returns and tracking the costs and reasons associated with them can increase revenues, lower costs, improved profitability and enhanced levels of customer service. Using Dynamics GP’s RMA module can help and analyze that data in Power BI is one of the things that I’m currently working on. Below is a screenshot of that analysis and a subject of a future blog post.

Sales Returns Dashboard

Ready to Do Even More with Dynamics and Power BI?

Stay tuned for more help in lead your organization into becoming data-driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP journey.

Enhancing our Inventory Reorder Point Analysis in Power BI

In my original Inventory Reorder Analysis report, I’m using some the technics discussed by Belinda Allen in her Inventory Item Reorder Dashboard. I converted it to Power BI and added a little extra by also analyzing SOP product sales data and computing “Inventory Logic” based on whether the “Inventory Sold in Time Period” columns total is three times the current “Qty Available” producing a report that looked like the screen shot below.

Original Inventory Re-Order Analysis

Enhancing the Inventory Reorder Analysis

A good Inventory Reorder analysis includes the recalculations to forecast future demand and safety stock find your reorder point. For the sales quantity forecasts for my lead time demand, I’m going to use the DAX time series discussed here by Fountain Analytics. After following time series instructions my sales forecast results looked like this.

Sales Quantity Usage Forecast

With the Sales forecast quantity usage in place, I then needed to calculate our Safety Stock based on the below formula.

Safety Stock Formula

Using the data from the Procurement Analysis and the Sales Analysis reports, I calculated the four data points with the below DAX formulas.

Max Days Consumption =

MAXX (

VALUES ( Dates[Date] ),

MAXX(

VALUES( ‘Products'[ItemNumber] ),

‘Sales Measures'[Units Sold]

))

Max Days Inventory Lead Time =

MAXX (

VALUES ( Dates[Date] ),

MAXX(

VALUES( ‘Products'[ItemNumber] ),

[Lead Time]

))

Average Days Consumption =

AVERAGEX (

VALUES ( Dates[Date] ),

AVERAGEX(

VALUES( ‘Products'[ItemNumber] ),

‘Sales Measures'[Units Sold]

))

Average Days Inventory Lead Time =

AVERAGEX (

VALUES ( Dates[Date] ),

AVERAGEX(

VALUES( ‘Products'[ItemNumber] ),

[Lead Time]

))

You put it all together to your Reorder Point – (Lead time demand) + (safety stock) = Reorder Point. The end result is the updated Inventory Reorder Analysis report shown below.

Update Inventory Re-Order Point Analysis

Updated Template

You can download the updated template here. In the template, I also updated the Inventory Reorder Point report using the New Matrix functionality.

Stay tuned for the future developments of the reports and an overview of their functionality and for help with building reports like this.

Analyzing Dynamics GP Procurement Transactions with Power BI

Procurement is an important part of any business. Regularly analyzing this data is easier with the use of Power BI to ensure that you are making the best use of your company’s resources. Here are a couple of thing to look at when analyzing your purchasing department – Cost and Delivery Time Analysis.

Cost Analysis
There are several things to look at when analyzing Inventory Costs. Obviously, the unit cost of the item but we also have to look at delivery, handling, storage, deposits and item returns from customers.  In the Power BI analysis below, I’m simply looking and the Item’s Current Cost from Dynamics GP Inventory Item Card vs. the Average purchasing cost and per order unit costs.

Delivery/Lead Time Analysis
Delivery/Lead times can be important to companies, particularly when perishable and large goods are concerned.  Delivery/Lead times that start to creep higher will hamper your Item Resource Planning process. Staff need to analysis delivery/lead times and plan accordingly to prevent stock outs and limit storage area and cash resource needs.

Putting It All Together
I first created the below SQL view in my Dynamics GP company.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[vw_POPTransactions]
AS
    WITH    CTE
              AS ( SELECT   [PONUMBER] ,
                            [ORD] ,
                            [POLNESTA] ,
                            [POTYPE] ,
                            [ITEMNMBR] ,
                            [ITEMDESC] ,
                            [VENDORID] ,
                            [VNDITNUM] ,
                            [VNDITDSC] ,
                            [NONINVEN] ,
                            [LOCNCODE] ,
                            [UOFM] ,
                            [UMQTYINB] ,
                            [QTYORDER] ,
                            [QTYCANCE] ,
                            [UNITCOST] ,
                            [EXTDCOST] ,
                            [INVINDX] ,
                            [REQDATE] ,
                            [PRMDATE] ,
                            [PRMSHPDTE] ,
                            [REQSTDBY] ,
                            [COMMNTID] ,
                            [DOCTYPE] ,
                            [POLNEARY_1] ,
                            [POLNEARY_2] ,
                            [POLNEARY_3] ,
                            [POLNEARY_4] ,
                            [POLNEARY_5] ,
                            [POLNEARY_6] ,
                            [POLNEARY_7] ,
                            [POLNEARY_8] ,
                            [POLNEARY_9] ,
                            [DECPLCUR] ,
                            [DECPLQTY] ,
                            [ITMTRKOP] ,
                            [VCTNMTHD] ,
                            [BRKFLD1] ,
                            [JOBNUMBR] ,
                            [COSTCODE] ,
                            [COSTTYPE] ,
                            [ORUNTCST] ,
                            [OREXTCST] ,
                            [LINEORIGIN] ,
                            [FREEONBOARD] ,
                            [ODECPLCU] ,
                            [Product_Indicator] ,
                            [Source_Document_Number] ,
                            [Source_Document_Line_Num] ,
                            [RELEASEBYDATE] ,
                            [Released_Date] ,
                            [Purchase_IV_Item_Taxable] ,
                            [Purchase_Item_Tax_Schedu] ,
                            [Purchase_Site_Tax_Schedu] ,
                            [PURCHSITETXSCHSRC] ,
                            [BSIVCTTL] ,
                            [TAXAMNT] ,
                            [ORTAXAMT] ,
                            [BCKTXAMT] ,
                            [OBTAXAMT] ,
                            [Landed_Cost_Group_ID] ,
                            [SHIPMTHD] ,
                            [LineNumber] ,
                            [ORIGPRMDATE] ,
                            [FSTRCPTDT] ,
                            [LSTRCPTDT] ,
                            [RELEASE] ,
                            [ADRSCODE] ,
                            [CMPNYNAM] ,
                            [CONTACT] ,
                            [ADDRESS1] ,
                            [ADDRESS2] ,
                            [ADDRESS3] ,
                            [CITY] ,
                            [STATE] ,
                            [ZIPCODE] ,
                            [CCode] ,
                            [COUNTRY] ,
                            [PHONE1] ,
                            [PHONE2] ,
                            [PHONE3] ,
                            [FAX] ,
                            [ADDRSOURCE]
                   FROM     POP10110
                   UNION ALL
                   SELECT   [PONUMBER] ,
                            [ORD] ,
                            [POLNESTA] ,
                            [POTYPE] ,
                            [ITEMNMBR] ,
                            [ITEMDESC] ,
                            [VENDORID] ,
                            [VNDITNUM] ,
                            [VNDITDSC] ,
                            [NONINVEN] ,
                            [LOCNCODE] ,
                            [UOFM] ,
                            [UMQTYINB] ,
                            [QTYORDER] ,
                            [QTYCANCE] ,
                            [UNITCOST] ,
                            [EXTDCOST] ,
                            [INVINDX] ,
                            [REQDATE] ,
                            [PRMDATE] ,
                            [PRMSHPDTE] ,
                            [REQSTDBY] ,
                            [COMMNTID] ,
                            [DOCTYPE] ,
                            [POLNEARY_1] ,
                            [POLNEARY_2] ,
                            [POLNEARY_3] ,
                            [POLNEARY_4] ,
                            [POLNEARY_5] ,
                            [POLNEARY_6] ,
                            [POLNEARY_7] ,
                            [POLNEARY_8] ,
                            [POLNEARY_9] ,
                            [DECPLCUR] ,
                            [DECPLQTY] ,
                            [ITMTRKOP] ,
                            [VCTNMTHD] ,
                            [BRKFLD1] ,
                            [JOBNUMBR] ,
                            [COSTCODE] ,
                            [COSTTYPE] ,
                            [ORUNTCST] ,
                            [OREXTCST] ,
                            [LINEORIGIN] ,
                            [FREEONBOARD] ,
                            [ODECPLCU] ,
                            [Product_Indicator] ,
                            [Source_Document_Number] ,
                            [Source_Document_Line_Num] ,
                            [RELEASEBYDATE] ,
                            [Released_Date] ,
                            [Purchase_IV_Item_Taxable] ,
                            [Purchase_Item_Tax_Schedu] ,
                            [Purchase_Site_Tax_Schedu] ,
                            [PURCHSITETXSCHSRC] ,
                            [BSIVCTTL] ,
                            [TAXAMNT] ,
                            [ORTAXAMT] ,
                            [BCKTXAMT] ,
                            [OBTAXAMT] ,
                            [Landed_Cost_Group_ID] ,
                            [SHIPMTHD] ,
                            [LineNumber] ,
                            [ORIGPRMDATE] ,
                            [FSTRCPTDT] ,
                            [LSTRCPTDT] ,
                            [RELEASE] ,
                            [ADRSCODE] ,
                            [CMPNYNAM] ,
                            [CONTACT] ,
                            [ADDRESS1] ,
                            [ADDRESS2] ,
                            [ADDRESS3] ,
                            [CITY] ,
                            [STATE] ,
                            [ZIPCODE] ,
                            [CCode] ,
                            [COUNTRY] ,
                            [PHONE1] ,
                            [PHONE2] ,
                            [PHONE3] ,
                            [FAX] ,
                            [ADDRSOURCE]
                   FROM     POP30110
                 )
    SELECT  a.POPIVCNO ,
            a.IVCLINNO ,
            a.POPRCTNM ,
            a.RCPTLNNM ,
            b.VENDORID ,
            b.VENDNAME ,
            b.VNDDOCNM AS [Invoice No] ,
            b.receiptdate AS [Invoice Date] ,
            f.Released_Date ,
            DATEDIFF(day, f.Released_Date, c.receiptdate) AS Lead_Time ,
            f.PONUMBER ,
            c.VNDDOCNM AS [Bill of Lading] ,
            c.receiptdate AS [Date Received] ,
            d.ITEMNMBR ,
            d.ITEMDESC ,
            d.VNDITNUM ,
            d.VNDITDSC ,
            a.QTYINVCD ,
            e.QTYSHPPD ,
            e.QTYREJ ,
            f.UNITCOST
    FROM    POP10600 a
            INNER JOIN POP30300 b ON a.POPIVCNO = b.POPRCTNM
            INNER JOIN POP30300 c ON a.POPRCTNM = c.POPRCTNM
            INNER JOIN POP30310 d ON a.POPRCTNM = d.POPRCTNM
                                     AND a.RCPTLNNM = d.RCPTLNNM
            INNER JOIN CTE f ON d.PONUMBER = f.PONUMBER
                                AND d.RCPTLNNM = f.ORD
            INNER JOIN POP10500 e ON a.POPRCTNM = e.POPRCTNM
                                     AND a.RCPTLNNM = e.RCPTLNNM

Next I imported the data into my Power BI Desktop pbix file, and created DAX measures for Total Lead Time, Total purchasing Unit Cost, Average Lead Time, Average Purchasing Unit Cost and Lead Time Weekly Moving Average. The end result is the Power BI report below that compares:

  • Item Current Cost vs. Average Purchasing Unit Cost and order detailed costs
  • Average Lead Time vs. per order lead times

clip_image002

Future Dashboard developments

In the coming months, I plan additional Power BI reports associated with my Dynamics GP data. The report will include an updated inventory procurement analysis using the lead time analysis from this post and sales forecasts

Power BI, R and Marketing Campaign Analysis

For the second post in a row, I’m going to talk about casino performance analysis so I’m feeling a little like Marty McFly from Back to the Future. One of the biggest struggles we had was with our direct mail offers to our players’ club members. A proper direct mail offer campaign would be setup with a control group so we could perform analysis against the control group, but try to tell that to the A tier player that didn’t get an offer or a different amount then the other A tier players.

Without the control group, we would calculate the difference between the average coin in before and after the direct mail offer was received to judge our incremental revenue and evaluate whether the campaign was successful. This method was very simplistic because it assumed all differences between performance before and after the interaction are due strictly to that direct mail offer. Google set out to help digital marketers with this problem of no control group in 2014 and we can use their Causal Impact R package to analyze our casino’s direct mail offers as well.

Putting it All to Use

Using the same daily Coin in (gross sales) data from the last blog on “Use Power BI and R to Quickly Identify Business Insights”, we need to complete the following steps:

  1. Install the following R packages
    1. install.packages(“devtools”)
    2. install.packages(“BoomSpikeSlab”)
    3. install.packages(“dtw”)
    4. install.packages(“chron”)
    5. devtools::install_github(“google/CausalImpact”)
  2. In Power BI Desktop, select the “R” in the Visualizations section and then drag the Denomination, Date and Revenue fields from the Coin In tables into the Values section to build our dataset for are R code.

Power BI Desktop

3. Add the following code in the R Script editor

library(CausalImpact)

library(data.table)

productsM <- melt(dataset, id=1:2)

productsC <- dcast(productsM, Date ~ Denomination + variable, sum)

colnames(productsC) <- make.names(colnames(productsC))

products <- zoo(productsC[,2:ncol(productsC)],as.Date(productsC[,1]))

pre.period <- as.Date(c(“2008-01-01”, “2008-10-03”))

post.period <- as.Date(c(“2008-10-04”, “2008-11-15”))

impact <- CausalImpact(products, pre.period, post.period, model.args=list(nseasons=7))

plot(impact)

#summary(impact)

4. Click the run button to execute the R script

Power BI R Script

Understanding the Results

The Casual Impact R package reduces the below three graphs

Original: Solid, black line is observed data before the intervention and dotted, blue line is the predicted coin in values for what would have occurred without the intervention

Pointwise: The net difference between the observed and predicted response on the original scale, or the difference between the solid, black line and the dotted, blue line on the original graph.

Cumulative: Dotted, blue line is the individual causal effects added up in time, day after day.

For all three graphs, the light blue shaded area represents the results in a 95% confidence level. The farther that the graph extends past the beginning of the intervention, the less certain of the causal effect; hence, the larger the shaded area.Power BI R visualization

Conclusion

Hope this helps you with your marketing campaign analysis in your organization.