Mastering Dynamic Stock Management with Cumulative Measures in Power BI

[Ilgar_Zarbali]

Step-by-Step Process

We’ve defined two key measures to display sold and purchased quantities:

• Total Sold Qty:
  
  Spoiler

  Total Sold Qty = SUM(fSales[Q-ty])

• Total Purchased Qty:
  
  Spoiler

  Total Purchased Qty = SUM(fPurchasing[Q-ty])

• Stock Measure:
  
  Spoiler

  Stock = [Total Purchased Qty]-[Total Sold Qty]

When adding these measures to the matrix visual, the results for January 2016 are correct. However, from February 2016 onward, negative stock values appear, which do not provide a meaningful view of the stock dynamics. To fix this, we need cumulative totals—tracking purchases and sales from the start date up to a specific date. The stock balance will then be calculated as the difference between these cumulative measures.

Cumulative Measures

To define cumulative measures, we use CALCULATE with FILTER and ALL functions to accumulate the quantities dynamically by date:

• Cumulative Sold Qty:
  
  Spoiler

  Cumulative Sold Qty =
  CALCULATE(
       [Total Sold Qty], 
       FILTER(
           ALL('Calendar'[Date]),
          'Calendar'[Date] <= MAX('Calendar'[Date])
     )
  )
• Cumulative Purchased Qty:
  
  Spoiler

  Cumulative Purchased Qty =
  CALCULATE(
           [Total Purchased Qty],
           FILTER(
                ALL('Calendar'[Date]),
                'Calendar'[Date] <= MAX('Calendar'[Date])
          )
  )

Using these cumulative measures, we can now calculate the stock balance effectively.

• Stock Measure:
  
  Spoiler

  Stock = [Cumulative Purchased Qty] - [Cumulative Sold Qty]

Optimizing the Matrix Visual

After removing the individual sold and purchased measures from the matrix visual and adding the cumulative sold, cumulative purchased, and stock measures, the results become accurate and dynamic. The stock balance now reflects the real-time state by considering cumulative quantities.

You can further refine the visual by adding stores to the matrix, enabling stock tracking at the store level across months. Additionally, removing the month granularity will display the stock per store as of the latest date. You can also track the stock balance per product group alongside stores to gain deeper insights into inventory distribution.

Conclusion

Using cumulative calculations ensures that stock balances are dynamically tracked, reflecting the accurate movement of goods over time. This approach helps avoid negative or misleading stock values and provides the flexibility to analyze stock by stores, products, or groups at any point in time. The cumulative measures create a robust foundation for meaningful insights, supporting better decision-making in inventory management.