Stock on hand calculations and stock counts

DAX DAX Calculations
1

Hi All,
I have been tasked with creating a stock on hand report by my supervisor.
The dataset im working with contains logistics transactions, with the following columns.

  1. Date
  2. Product
  3. Warehouse
  4. Quantity
  5. MovementTypeDesc

All movement types except “StockCount” can be summarized cumulatively to get the stock on hand total at a given date.

The “StockCount” type on the other hand is the actual result of the stock count, so to include this into the cumulative sum, i need to “reset” the count

As far as i can see, i have successfully done this in the attached model, and it works fine on the dummydataset, but its very slow when working with the actual dataset.

Quantity Cumulative = 
VAR CurrentDate = MAX(DIM_Dates[Date])
VAR CurrentWarehouse = MAX(FACT_StockMovement[Warehouse])
VAR CurrentProduct = MAX(FACT_StockMovement[Product2])

VAR LastStockCountDate = 
    CALCULATE (
        MAX ( FACT_StockMovement[Date] ),
        FILTER (
            ALL ( FACT_StockMovement ),
            FACT_StockMovement[Warehouse] = CurrentWarehouse &&
            FACT_StockMovement[Product2] = CurrentProduct &&
            FACT_StockMovement[Date] < CurrentDate &&
            FACT_StockMovement[MovementTypeDesc] = "StockCount"
        )
    )

VAR CumulativeQuantityBeforeStockCount =
    CALCULATE (
        SUM ( FACT_StockMovement[Quantity] ),
        FILTER (
            ALL ( FACT_StockMovement ),
            FACT_StockMovement[Warehouse] = CurrentWarehouse &&
            FACT_StockMovement[Product2] = CurrentProduct &&
            FACT_StockMovement[Date] <= CurrentDate &&
            FACT_StockMovement[Date] > LastStockCountDate
        )
    )

VAR LastStockCountQuantity = 
    CALCULATE (
        MAX ( FACT_StockMovement[Quantity] ),
        FILTER (
            ALL ( FACT_StockMovement ),
            FACT_StockMovement[Warehouse] = CurrentWarehouse &&
            FACT_StockMovement[Product2] = CurrentProduct &&
            FACT_StockMovement[Date] = LastStockCountDate &&
            FACT_StockMovement[MovementTypeDesc] = "StockCount"
        )
    )

RETURN
IF (
    ISBLANK ( LastStockCountDate ),
    CALCULATE (
        SUM ( FACT_StockMovement[Quantity] ),
        FILTER (
            ALL ( FACT_StockMovement ),
            FACT_StockMovement[Warehouse] = CurrentWarehouse &&
            FACT_StockMovement[Product2] = CurrentProduct &&
            FACT_StockMovement[Date] <= CurrentDate
        )
    ),
    CumulativeQuantityBeforeStockCount + LastStockCountQuantity
)

Also, i cannot wrap my head around how to get the stock in han on any given date. In the attached solution it only gives me the stock on hand when there is a movement or a stock count present for the combination “date-product-warehouse”

So my questions are:

  1. Any performance improvement tips? Either on the DAX code or modelling changes in Power Query (The source is an on prem SQL db)
  2. How do i get the daily stock on hand, regardless if there is a “date-product-warehouse” combination present in the dataset?

DummyFile.xlsx (43.1 KB)
stockmovementcase.pbix (68.9 KB)

2

Hi @andeei ,

While waiting for the community to review and respond to your issue, take advantage of Data Mentor. It offers a wealth of tools and resources that could provide immediate solutions and enhance your report-building efficiency.

Cheers,

Enterprise DNA Support Team

3

@andeei Try this optimized code and let me know if it works faster in huge data set Quantity Cumulative =
VAR CurrentDate = MAX(DIM_Dates[Date])
VAR CurrentWarehouse = MAX(FACT_StockMovement[Warehouse])
VAR CurrentProduct = MAX(FACT_StockMovement[Product2])

VAR LastStockCountData =
CALCULATETABLE (
TOPN (
1,
FILTER (
ALL (FACT_StockMovement),
FACT_StockMovement[Warehouse] = CurrentWarehouse &&
FACT_StockMovement[Product2] = CurrentProduct &&
FACT_StockMovement[Date] < CurrentDate &&
FACT_StockMovement[MovementTypeDesc] = “StockCount”
),
FACT_StockMovement[Date], DESC
)
)

VAR LastStockCountDate = MAXX(LastStockCountData, FACT_StockMovement[Date])
VAR LastStockCountQuantity = MAXX(LastStockCountData, FACT_StockMovement[Quantity])

VAR CumulativeQuantity =
CALCULATE (
SUM (FACT_StockMovement[Quantity]),
FILTER (
ALL (FACT_StockMovement),
FACT_StockMovement[Warehouse] = CurrentWarehouse &&
FACT_StockMovement[Product2] = CurrentProduct &&
FACT_StockMovement[Date] <= CurrentDate &&
(
ISBLANK(LastStockCountDate) ||
FACT_StockMovement[Date] > LastStockCountDate
)
)
)

RETURN
IF (
ISBLANK (LastStockCountDate),
CumulativeQuantity,
CumulativeQuantity + LastStockCountQuantity
)