Forum Discussion

WILFR3D2K24's avatar
WILFR3D2K24
New Member
2 years ago
Solved

DAX Calculated Column Creation for Inventory Tracking

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.

"Calculated Column"
"DAX help"
"DAX"
Need DAX Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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]
)

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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]
)