Stock Movement Intelligence in Power BI

8th of December 2017 | Blog

In this blog post, Madushan Dahanayake talks about how he self-acquainted the features of Power BI as he was looking forward to a career in business intelligence. Madushan joined Fortude as an Associate BI Consultant in 2017 March after graduating from University at Buffalo, New York.

During the last few weeks, I have been getting myself acquainted with Power BI and its features. Finding data sources (to practice with), cleaning them, creating data models, and creating reports and dashboards have been very interesting. Ultimately, I was given the challenge to develop a Stock Movement Intelligence Solution for the ‘WideWorldImportersDW’ sample database for SQL Server 2016. You can access this database here.

This is a snapshot of the database set up:

The database is already set up with Dimension tables and Fact tables. Hence, coming up with the data model was not a challenge. The only thing left for me was to figure out which fields I need to get together to help me solve the problem. The ‘Quantity on Hand’ field was the most important as it provided the most recent stock information we had.

In the end, I constructed the following table.

Then I had to figure out a way to filter the products that were due to be filled on the stock. I wanted to give business users the flexibility to choose the information they wanted to display on the reports. As a result, I thought of creating fields that were ‘sliceable.’ Using the IF function in two Calculated Columns allowed me to categorize the data at ‘Reorder’ and ‘Target Stock’ levels.

One of the main objectives of this project was to alert hypothetical report users when a certain stock item goes out of stock. With the data I have, the easiest solution to this was to find the average monthly sales for an item and to calculate how many months it would take to deplete our current stock. To make it even more useful for the business users, I further refined to show the number of days left to reorder an item, considering the lead time for each product.

At this point, I was quite happy with how the solution turned out, but I wanted to see if I could do further analysis that might be useful for business users. Again, based on the data I was working with, the only other insight I could think of was to try and account for seasonality. Where earlier I calculated average monthly sales for an item, this time I calculated average sales for a given month and an item.

By doing this, I could show seasonal demand for any item. A business user can now see how the demand would look like for any item up for re-order in the coming months and adjust orders accordingly.

Additionally, I created sales and profit analysis reports on the same database. For this, I used a comparatively straightforward process where I pulled data from the sales fact table.

The final Stock Movement report looked like this.

There you have it. My first documented foray into BI. Hope you found it educational and interesting.

Madushan Dahanayake
Associate Consultant – BI, Project Delivery

Related Posts

From insights to thought-leadership

View More

#1724 – The Booth you need to visit at Inforum 2019!

Inforum 2019 has a lot to offer with rich content being discussed at speed sessions and guest talks, along with some of the industry’s best entertainers. With a week left to go - this Inforum, we’re giving you a few good reasons as to why you need to visit Booth #1724.

Earth.Ocean.Life launched on World Environment Day

World Environment Day by the United Nations was found to encourage international awareness to protect our environment.

The ERP Discovery Study – A Simple Bare Necessity!

Years of experience in implementing ERPs across multiple verticals have taught implementation partners the value of a comprehensive discovery phase – they make implementing ERP simpler.

#MoveIt launched on World Health Day

Fortude launched a global virtual step challenge titled #MoveIt on 7th April to coincide with World Health Day 2019.

Fortude launches its Diversity and Inclusion initiative, ‘I AM DIVERSITY’

On the 18th of January, Fortude launched the ‘I AM DIVERSITY’ initiative, as part of its corporate ethos to encourage a diverse and inclusive workplace for all employees irrespective of age, gender, race, color, orientation and disability.

Webinar Blog: Reduce Costs and Simplify the Testing Process

We are the laziest and the most impatient version of homo-sapiens ever to exist on the planet Earth.