Using Power BI for Planning (Warehouse) with Stock Values

Author:Murphy | View: 27883 | Time: 2025-03-22 21:11:13

Stock Measures is nothing new in Power BI. But how can we use Power BI to plan for the future using these Measures? Here, I will design a scenario and explain how I solved it.

Scenario

Let's start with the scenario:

My company, Contoso Ltd, aims to consolidate the warehouses with product stock into regional warehouses, limiting the warehouse space in the local stores and thus increasing the sales area.

For Planning, I need the volume for each product and to categorize it into five categories:

Very Small
Small
Medium
Large
Unknown (For products without size information)

I could add more categories, for example, by using the area and adding the height. However, I decided to use this approach to simplify things.

My company stores the Stock Data in a database weekly, which is my source for Power Bi.

My approach to creating the planning data is to calculate the Stock volume for each month using the maximum amount of Stock in all Stores in a specific area.

With this figure, I can plan the size of the new Warehouses and allocate space for each size category.

Therefore, I need the stock numbers per month and overall.

Again, I decided to go with the existing data for the States for simplicity because I have not created geographic areas for the central warehouses.

In reality, I would ask the business department to create custom geographic areas based on the planned stores' locations. There would be many more details or other groupings of the products that would be of interest. But bear with me if I simplify the story for this article.

Here are the values of interest for Bavaria (Germany) grouped by Volume Group for two months:

Figure 1 – Values of interest for Bavaria by Volume category for two months (Figure by the Author)

As you can see, the highest Stock volumes are distributed without any rule and can change monthly.

I need to know the maximum number of units per Size category for each month and each State.

The details of each store are not interesting to my analysis.

Neither is the number of units per week.

When I take the example from the screenshot above, and I look at March for Bavaria, the result for March should be:

332 Very Small-sized Units
168 Small sized Units
72 Medium-sized units
84 Large-sized Units
204 Units without information about the Size

These numbers will be a starting point for the planning and should be in the result for Bavaria.

Designing the solution

If you are new to calculating Stock Measures in Power BI, or how we call them "Semi-Additive Measures", look at the References section below to find two more articles on this topic.

To design the solution, I must first understand my data.

The data model is the following:

Figure 2 – Base Data model (Figure by the Author)

As mentioned above, the Inventory data is collected weekly for each Store for each product in the Inventory Data Fact table.

Which information do I need for my analysis:

The Calendar
The Products volume category (Column Volume Range)
The State of each Store. Perhaps together with the Country
The Inventory

I need a table with these columns to calculate the results.

I have two possible approaches to solving the challenge with such a table:

Create a DAX Measure, which constructs an intermediary table and performs the calculations on the fly.
Create a calculated DAX table with the necessary columns.

The first approach could be practical; I don't need to store additional data in the data model or memory.

However, it can cause issues with performance and memory consumption.

As I must reuse the table's results, I can copy the logic for the intermediary table multiple times or be forced to calculate the table on the fly numerous times, even when I create a Base Measure and reuse it.

Based on these requirements and possible side effects, I will use a DAX table for my calculations.

After adding this table, my Data model will look like this:

Figure 3 – Expanded Data model with the calculated table (Figure by the Author)

As you can see, the calculated table will only be related to the Date table. It will be self-contained, as the Granularity differs from the original Inventory table.

Creating the solution

To create the table, I use the following DAX query:

EVALUATE
    SUMMARIZECOLUMNS('Inventory'[DateKey]
                    ,'Inventory'[DateLastDayOfMonth]
                    ,'Geography'[State Province]
                    ,'Product'[Volume Range]
                    ,'Product'[Volume Range Sort]
                    ,"Inventory", [Max Stock Volume]
                    )

The Measure [Max Stock Volume] gets the maximum value from the Inventory table, in this case, for each Month and all Stores in each State.

The result of the query is the following:

Figure 4 – Result of DAX Query for the calculated table (Figure by the Author)

As mentioned before, the Measure [Max Stock Volume] contains a simple Expression:

Max Stock Volume = MAX('Inventory'[OnHandQuantity])

The existing Measure to Calculate the Stock Volume in my report contains a logic to get the latest stock volume. This is a standard Measure using the LASTNONBLANKVALUE() function.

I don't need this logic, as I don't summarize the Stock volume over time in the calculated table.

Next, I use the New table function to create the calculated table using the SUMMARIZECOLUMN() function from the query above:

Figure 5 – Create the calculated table with the Query above (Figure by the Author)

Another approach would be to create this table in Power Query. This would lead to a more efficient compression of the data.

In my case, the resulting table has only 57'005 rows. Therefore, I refrain from doing this, as it would involve multiple Merge operations without that much of a benefit. But if the table would contain hundreds of thousands or even millions of rows, I would do it in that way,

I use the column DateLastDayOfMonth to create the relationship to the Date table:

Figure 6 – Add the new Relationship to the Date table (Figure by the Author)

Next, I calculate the highest stock volume for each State, each Volume Range, and the current month.

To achieve this, I add a calculated column to the calculated table.

This calculated column gets the current month (DateLastDayOfMonth), the State, and the Volume Range.

Then, I get the maximum Stock volume.

The DAX code for this column is this:

Highmark Stock Volume = 
    VAR CurrentMonth = 'Inventory Analysis table'[DateLastDayOfMonth]
    VAR CurrentState = 'Inventory Analysis table'[State Province]
    VAR CurrentVolumeRange = 'Inventory Analysis table'[Volume Range]

RETURN
    CALCULATE(
            MAX('Inventory Analysis table'[Inventory])
            ,REMOVEFILTERS('Inventory Analysis table')
            ,'Inventory Analysis table'[DateLastDayOfMonth] = CurrentMonth
            ,'Inventory Analysis table'[State Province] = CurrentState
            ,'Inventory Analysis table'[Volume Range] = CurrentVolumeRange
            )

The result for a Bavaria looks like this:

Figure 7 – Result of calculated column with multiple rows (Figure by the Author)

Although this result is correct and corresponds with the data shown at the beginning, it is less useful than it can be.

I can use it with a Measure that considers only the latest value, but the fact that I need the result in a monthly report complicates this approach.

Therefore, I apply an additional logic to the calculation for this column to return only the value for the latest date:

Highmark Stock Volume = 
    VAR CurrentMonth = 'Inventory Analysis table'[DateLastDayOfMonth]
    VAR CurrentState = 'Inventory Analysis table'[State Province]
    VAR CurrentVolumeRange = 'Inventory Analysis table'[Volume Range]

    VAR ResultPerMonth = 
        CALCULATE(
                MAX('Inventory Analysis table'[Inventory])
                ,REMOVEFILTERS('Inventory Analysis table')
                ,'Inventory Analysis table'[DateLastDayOfMonth] = CurrentMonth
                ,'Inventory Analysis table'[State Province] = CurrentState
                ,'Inventory Analysis table'[Volume Range] = CurrentVolumeRange
                )

    VAR LastDatePerMonth = 
        CALCULATE(
                MAX('Inventory Analysis table'[DateKey])
                ,REMOVEFILTERS('Inventory Analysis table')
                ,'Inventory Analysis table'[DateLastDayOfMonth] = CurrentMonth
                ,'Inventory Analysis table'[State Province] = CurrentState
                ,'Inventory Analysis table'[Volume Range] = CurrentVolumeRange
                )

RETURN
    IF( 'Inventory Analysis table'[DateKey] = LastDatePerMonth, ResultPerMonth)

This is the result of this calculation:

Figure 8 – Corrected result for the Highmark column (Figure by the Author)

Now I can add a Measure with a simple SUM over this column to get the needed result:

Stock for Highmark = SUM('Inventory Analysis table'[HighMark Stock Volume])

When displaying the results by month, I get the needed results:

Figure 9 – Result for Q1 2008 per State and Volume Range (Figure by the Author)

As you can see, the result is the same as the screenshot shown at the beginning.

In case I need to add the Country and the Continent, I can either:

Add a Relationship to the Geography table to the State column.
Add the Country and Continent columns to the calculated table.

Both variants lead to the same result.

However, in my current configuration, I cannot add a Relationship because of a circular dependency, which can be caused by either the existing State column or the implemented calculated columns and Measure.

As a consequence, it is easier to add the two columns to the calculated table:

Figure 10 – Calculated table with the newly added Country and Continent columns (Figure by the Author)

I can still use the results with the new hierarchy, as I will not summarize the results over time.

Aggregation over multiple months would be wrong, and that's why such Measures are called "Semi-Additive Measures."

Therefore, I should remove the Row Totals from the Matrix visual to avoid displaying the wrong information.

What's next?

Now, I can expand the table to enable other analyses, such as by Product Category, to add more analysis possibilities.

But take care.

When you add a new column, the column [HighMark Stock Volume] will produce duplicated values, as it will not account for the new column:

Figure 11 – Calculated table with added Product Category and duplicated values (Figure by the Author)

To correct this, I must add checks for the Category to ensure that the value remains unique:

Figure 12 – Check for the added Category column (Figure by the Author)

At some point, it would make sense to either:

Change the logic in the Measure to ignore the duplicates or
Add a new calculated table for the specific analysis needs.

The first approach could be complicated, and the second approach will expand the Data model for each analysis.

So, both have pros and cons.

Conclusion

This approach delivers the needed result, but it has some drawbacks:

The calculated table is self-contained and has no relationship to other tables except for the date.

a. This means that I cannot use the columns from the Geography table to filter visuals based on this table and the Inventory table.

This means that the data is duplicated in the data model.
Adding further aspects to analyze can cause complicated effects in the calculations.
I must always add the Month to any analysis, as the result can be misleading without it.

But it's a relatively simple solution to the problem.

Such planning analysis is usually not done regularly and could be discarded after the planning process is completed.

This alleviates many of the drawbacks mentioned above.

One of my clients needs such an analysis for ongoing planning, and he is happy to cope with these drawbacks and to work around them.

Therefore, I did not need to build a more sophisticated solution.

This is one key lesson of this article: Search for a good enough solution instead of spending days or weeks searching for the perfect solution.

The perfect solution can be too complex without any added benefits.

References

Here are some past articles on how to work with Stock Measures:

Going the Next Step with Stock Measures in DAX

Refinement of Semi-Additive Measures in DAX

I wrote an article some time ago about changing the granularity of the data, which is a similar problem.

Here is the link to it:

Changing granularity of data in Power BI

I use the Contoso sample dataset, as I did in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described here.

Get an email whenever Salvatore Cagliari publishes.

I make my articles accessible to everyone, even though Medium has a paywall. This allows me to earn a little for each reader, but I turn it off so you can read my pieces without cost.

You can support my work, which I do during my free time, through

https://buymeacoffee.com/salvatorecagliari

Or scan this QR Code: