DAX Calculated Column Creation for Inventory Tracking

[WILFR3D2K24]

Hi everyone,

I'm currently facing some difficulty in generating a calculated column using DAX, whereas it's relatively straightforward using Excel.

1. Below is an image displaying a sample of the dataset I'm currently working with:

This dataset illustrates the inventory of two products. I'm attempting to monitor the remaining units after each shipment.

2. The new column, named "Quantity," is what I'm aiming to create using DAX.

3. Here is the simple Excel formula that achieves the desired output:

The logic needs to take into consideration there multiple items within Category, so it can't simply substract the shipment/ or add the stock replenishment from the above value, this is where I suspect the CategoryIndex will come in handy.

Ideally, I would prefer a solution in DAX. However, if it can be accomplished using M Code, I'm also open to suggestions.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

WINDOW function (DAX) - DAX | Microsoft Learn

Quantity CC =
SUMX (
    WINDOW (
        1,
        ABS,
        0,
        REL,
        Data,
        ORDERBY ( Data[CategoryIndex], ASC, Data[Date], ASC ),
        ,
        PARTITIONBY ( Data[Category] ),
        MATCHBY ( Data[CategoryIndex], Data[Date] )
    ),
    Data[Stock Replenishment] + Data[Shipments]
)

[WILFR3D2K24]

Thank you, this worked!